cancel
Showing results for 
Search instead for 
Did you mean: 
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 ))

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
PBI Sept Update Carousel

Power BI September 2023 Update

Take a look at the September 2023 Power BI update to learn more.

Learn Live

Learn Live: Event Series

Join Microsoft Reactor and learn from developers.

Dashboard in a day with date

Exclusive opportunity for Women!

Join us for a free, hands-on Microsoft workshop led by women trainers for women where you will learn how to build a Dashboard in a Day!

MPPC 2023 PBI Carousel

Power Platform Conference-Power BI and Fabric Sessions

Join us Oct 1 - 6 in Las Vegas for the Microsoft Power Platform Conference.

Top Solution Authors
Top Kudoed Authors