Forum Discussion
Create measure for most recent value – dynamic
Hi, I’m trying to figure out a DAX solution to use the most recent value for future dates that are blank. The fact table looks something like this:
Date | Item ID | Total Sold |
1/31/2020 | 13 | 550 |
2/28/2020 | 543 | 130 |
3/31/2020 | 241 | 850 |
4/30/2020 | 98 | 370 |
5/31/2020 | 13 | 250 |
6/30/2020 | 543 | 400 |
7/31/2020 | 241 | 800 |
8/31/2020 | 98 | 150 |
9/30/2020 | 13 | 160 |
10/31/2020 | 543 | 140 |
11/30/2020 | 241 | 340 |
12/31/2020 | 98 | 500 |
1/31/2021 | 13 | 650 |
2/28/2021 | 543 | 400 |
3/31/2021 | 241 | 450 |
4/30/2021 | 98 | 500 |
The date table has dates from 1/1/2020 to 6/30/2021. What I want to be able to do is calculate the sum and for dates in the date table that are past the max date in the fact table, to use the most recent value. So this would look like:
Date | Sum |
1/31/2020 | 550 |
2/28/2020 | 130 |
3/31/2020 | 850 |
4/30/2020 | 370 |
5/31/2020 | 250 |
6/30/2020 | 400 |
7/31/2020 | 800 |
8/31/2020 | 150 |
9/30/2020 | 160 |
10/31/2020 | 140 |
11/30/2020 | 340 |
12/31/2020 | 500 |
1/31/2021 | 650 |
2/28/2021 | 400 |
3/31/2021 | 450 |
4/30/2021 | 500 |
5/31/2021 | 500 |
6/30/2021 | 500 |
Date | Sum Measure | Measure Result |
Q1 2020 | 550 + 130 + 850 | 2210 |
Q2 2020 | 370+250+400 | 1020 |
Q3 2020 | 800+150+160 | 1110 |
Q4 2020 | 140+340+500 | 980 |
Q1 2021 | 650 + 400 + 450 | 1500 |
Q2 2021 | 500 + 500 + 500 | 1500 |
