Forum Discussion
Solved
Show the latest value based on date column
Hi everyone, I have a table with a date-column and 5 different categories in their own column. I want a visualization in Power BI (table or card) to show the last value of each category based on ...
Hi, trndlnd
This problem of yours is better solved in Power Query.
Choose Date column-Unpivot Other Columns:
Then:
In the Power BI Desktop use Dax:
Measure = Var _lastdate=CALCULATE(MAX('Table'[Date]),FILTER(ALLEXCEPT('Table','Table'[Attribute]),[Value]<>BLANK())) RETURN CALCULATE(MAX('Table'[Value]),FILTER(ALLEXCEPT('Table','Table'[Attribute]),[Date]=_lastdate))Is this the result you expected?
Best Regards,
Community Support Team _Charlotte
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Kedar_Pande
Super User
Super UserFor Category 1:
Latest_Category_1 =
VAR LastDate = MAX('YourTable'[Date])
RETURN
CALCULATE(
LASTNONBLANK('YourTable'[Category 1], 1),
'YourTable'[Date] = LastDate
)
For Category 2:
Latest_Category_2 =
VAR LastDate = MAX('YourTable'[Date])
RETURN
CALCULATE(
LASTNONBLANK('YourTable'[Category 2], 1),
'YourTable'[Date] = LastDate
)
Repeat for Other Categories.
Now,
Select Card from the Visualizations pane.
Drag the measure (e.g., Latest_Category_1) onto the card.
Repeat for each measure
Thanks for your reply.
To me it looks like this should work, but I get an error. Don't know why.