Power Pivot Principles: Introducing the LASTDATE Function
6 August 2019
Welcome back to our Power Pivot blog. Today, we show you how the LASTDATE function work.
The LASTDATE function is a time intelligence function, just like the DATESINPERIOD function. We have covered the DATESINPERIOD function last week.
The LASTDATE function uses the following syntax to operate:
LASTEDATE( <date>)
The <date> parameter must be a column with dates. The <date> parameter can also be a table expression that will return a single column of dates.
The LASTDATE function returns with a table containing a single row and column with the latest date value from the input column from the <date> parameter.
In last week’s blog, we used the MIN function to determine the earliest date in the column. So why would we use the LASTDATE function?
Well, we can use the LASTDATE function with other expressions that require a table input and not a scalar input.
The other reason is that we can use filter functions that return with a table as the <date> input. Let’s say that we wish to retrieve the last date from the following dataset:
We can use this measure:
=LASTDATE(SaleDataJul1[Date])
Inserting this measure into a PivotTable:
It just returns with the respective row’s date, this is because the measure, as it stands, is subject to the row filters. If we remove the date column, we can see that the measure is returning with the last date in our data:
It seems that we will need to make some adjustments to our measure for it to return with the last date on each row:
=LASTDATE(
ALL(
SaleDataJul1[Date]
)
)
Including this measure in our PivotTable yields:
We have included the ALL function, so that it forces the LASTDATE function to disregard the row filters on each row. We can now use this measure to create more complex calculations in DAX, but more on that in a future blog…
That’s it for this week, come back next week for more Power Pivot. Until then happy pivoting!
Stay tuned for our next post on Power Pivot in the Blog section. In the meantime, please remember we have training in Power Pivot which you can find out more about >here. If you wish to catch up on past articles in the meantime, you can find all of our past Power Pivot blogs here.
