Forum Discussion
DAX populate values from different table for latest date
Hi there,
I'm completely new to Power BI and DAX, and have run into an issue that I do not know how to solve. I have 3 tables and need to create a matrix, that would populate values from each table.
Table 1:
| date | category | subcategory | status |
| 1/1/2023 | A | 1 | new |
| 1/1/2023 | A | 2 | ongoing |
| 1/1/2023 | A | 3 | new |
| 1/1/2023 | B | 1 | ongoing |
| 1/1/2023 | B | 2 | ongoing |
| 1/1/2023 | B | 3 | ongoing |
| 3/5/2023 | A | 1 | ongoing |
| 3/5/2023 | A | 2 | ongoing |
| 3/5/2023 | A | 3 | ongoing |
| 3/5/2023 | B | 1 | done |
| 3/5/2023 | B | 2 | done |
| 3/5/2023 | B | 3 | done |
| 5/7/2023 | A | 2 | done |
| 5/7/2023 | B | 3 | done |
Needed Matrix for the latest date (5/7/2023):
| 1 | 2 | 3 | |
| A | ongoing | done | ongoing |
| B | done | done | done |
I have also created two additional connected helper tables, one listing all 'categories', and another listing all 'subcategories'. That way when I build the matrix, I can add helper table values in rows and columns to avoid the matrix excluding values if there was no value reported for the most recent date.
Currently, I am only able to get a matrix that uses ONLY values for the latest date and omits the rest:
| 1 | 2 | 3 | |
| A | done | ||
| B | done |
To populate the matrix values, I am using a formula:
most recent status =
var thisdate = CALCULATE(MAX(table[date]))
var previousdate = CALCULATE(MAX(table[date]), table[date] < thisdate)RETURN IF(
thisdate <> BLANK(),
SELECTEDVALUE(table[status]),
(CALCULATE(MAX(table[status]), table[date] = previousdate)))
However, it will not add a 'status' that was reported previously but not for the most recent date. Instead, that cell will be left blank.
How do I adjust the measure to report previous values if they have not been reported for the most recent time period?
Thank you!
2 Replies
note to add: this matrix will be used with a "date" filter
