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

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
Anonymous
Not applicable

Running total dates too far on future or missing date

Hello,

 

I'm having trouble with running totals. My goal is to cut the DAX dates from the 2024 Q3 (red line), but dates go too far in future.

 

I'm having the following results with this DAX:

Measure = CALCULATE([Total sum],FILTER(ALL('Date'),'Date'[Date]<=MAX('Date'[date])))
Total sum = CALCULATE(SUM(FactTable[Value]),USERELATIONSHIP(FactTable[Date],'Date'[Date]))

 

BestUser1_1-1632123675980.png

If I try to use logic like this, I will miss the dates with blank values between the first and last dates, like this.

DAX: Measure = 

var _lastdate = CALCULATE(DATE(YEAR(MAX(FactTable[Date])),MONTH(MAX(FactTable[Date])),DAY(MAX(FactTable[Date]))),USERELATIONSHIP(FactTable[Date],'Date'[Date]))
return
IF(SELECTEDVALUE('Date'[Date])<=_lastdate,
CALCULATE([Total sum],FILTER(ALL('Date'),'Date'[Date]<=_lastdate))

BestUser1_2-1632124056273.png

 

 

My data is sorted like this:

1. Date table

1.1 Date table has dates from 1.1.2000 to 31.12.2049.

2. Fact table with inactive relationship with date table

2.1 Fact table has data for a singular date and value for each asset, which are due to complete in certain time in future.

 

Example:

Fact[Value]Fact[Date]
10001.5.2021
20001.8.2021
100001.7.2022
14591.1.2024

 

How do I overcome this problem?

 

Help is very much appreciated!

1 ACCEPTED SOLUTION
Anonymous
Not applicable

This does result in similar situation:

 

BestUser1_0-1632128403327.png

 

HOWEVER!

 

Total sum = CALCULATE(SUM(FactTable[Value]),USERELATIONSHIP(FactTable[Date],'Date'[Date]))

Measure =
var _max =maxx(allselected(FactTable), FactTable[Date])
return
CALCULATE([Total sum],FILTER(ALL('Date'),'Date'[Date]<=MAX('Date'[date]) && MAX('Date'[Date]) <= _max ))

BestUser1_0-1632129460700.png

 

This did help me to overcome this problem! Thank you so much!!

 

View solution in original post

2 REPLIES 2
amitchandak
Super User
Super User

@Anonymous , Try like

 

Total sum = CALCULATE(SUM(FactTable[Value]),USERELATIONSHIP(FactTable[Date],'Date'[Date]))

Measure =
var _max =maxx(allselected(FactTable), FactTable[Date])
return
CALCULATE([Total sum],FILTER(ALL('Date'),'Date'[Date]<=MAX('Date'[date]) && 'Date'[Date] <= _max ))

Share with Power BI Enthusiasts: Full Power BI Video (20 Hours) YouTube
Microsoft Fabric Series 60+ Videos YouTube
Microsoft Fabric Hindi End to End YouTube
Anonymous
Not applicable

This does result in similar situation:

 

BestUser1_0-1632128403327.png

 

HOWEVER!

 

Total sum = CALCULATE(SUM(FactTable[Value]),USERELATIONSHIP(FactTable[Date],'Date'[Date]))

Measure =
var _max =maxx(allselected(FactTable), FactTable[Date])
return
CALCULATE([Total sum],FILTER(ALL('Date'),'Date'[Date]<=MAX('Date'[date]) && MAX('Date'[Date]) <= _max ))

BestUser1_0-1632129460700.png

 

This did help me to overcome this problem! Thank you so much!!

 

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

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

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.

Join our Fabric User Panel

Join our Fabric User Panel

This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.