Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Backpacker4Life
Frequent Visitor

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

 

2 REPLIES 2
FreemanZ
Super User
Super User

hi @Backpacker4Life 

do you have dates for other days besides month end days

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors