Mynda
i am familiar with using LASTDATE or LASTNONBLANK to give me the last date for which a table has non-blank values.
But how do you make use of this info to showcase say the corresponding value for a column in the same table, or a related derived measure not even in the table? CALCULATE function doesn’t let me use measures in the filter expression.
ultimately, I want to be able to display such info in a multi card in PowerBI.
Thanks in advance.
Bob
Hi Bob,
It's not clear what you want to do or how your data is structured, but here is an example of using LASTNONBLANK with CALCULATE:
Latest Balance with formula :=
CALCULATE (
SUM ( Data[Balance] ),
LASTNONBLANK ( Calendar[Date], [Sum of Balance] )
)
This post explains more about those functions. If you want to exctract data from a related table then you may need to use the RELATED function.
An example file containing some data and a mock up of the PivotTable you're wanting to build would be helpful in giving you a more detailed answer.
Mynda
Thanks Mynda
I expressed my original question poorly.
I had created a measure based on table elements and was struggling to format things correctly, as it did not have a Table column. In the end, after discovering a lot more about the importance of using Date Table dates instead of dates that don't support Time Intelligence functions, I ended up with:
Latest Balance with formula :=
CALCULATE (
Measure ),
LASTNONBLANK ( Calendar[Date], Measure )
)
In my case, Measure was a quirky average price metric.
Bob
PS I will ensure future questions are much clearer!
