Create measure for most recent value – dynamic

Backpacker4Life — Sat, 25 Feb 2023 01:07:17 GMT

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

Re: Create measure for most recent value – dynamic

FreemanZ — Sat, 25 Feb 2023 03:41:18 GMT

hi Backpacker4Life

do you have dates for other days besides month end days

Re: Create measure for most recent value – dynamic

Backpacker4Life — Mon, 27 Feb 2023 16:13:33 GMT

Hi FreemanZ, I do not. I have that column connected to a date dimension table though.

topic Create measure for most recent value – dynamic in DAX Commands and Tips

Create measure for most recent value – dynamic

Re: Create measure for most recent value – dynamic

Re: Create measure for most recent value – dynamic