Forum Discussion
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 the date.
Example:
Table:
| Date | Category 1 | Category 2 | Category 3 | Category 4 | Category 5 |
| 08.10.2024 | Red | Green | |||
| 07.10.2024 | Green | Green | |||
| 06.10.2024 | Red | ||||
| 05.10.2024 | Green | Green | |||
| 04.10.2024 | Green | Red | |||
| 03.10.2024 | Green | ||||
| 02.10.2024 | Green | Green | |||
| 01.10.2024 | Red | Red | |||
| 30.09.2024 | Green | ||||
| 29.09.2024 | Red | Red |
The Visual should show:
Category 1: Red
Category 2: Green
Category 3: Green
Category 4: Green
Category 5: Red
(based on the latest value).
How can i manage this in Power BI?
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.
3 Replies
- Kedar_Pande
Super User
For 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 measureThanks for your reply.
To me it looks like this should work, but I get an error. Don't know why.
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.
