Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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 |
Hi @FreemanZ, I do not. I have that column connected to a date dimension table though.
User | Count |
---|---|
41 | |
27 | |
23 | |
19 | |
16 |
User | Count |
---|---|
55 | |
35 | |
21 | |
18 | |
15 |