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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

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]))

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))

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] 1000 1.5.2021 2000 1.8.2021 10000 1.7.2022 1459 1.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:

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 ))

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

2 REPLIES 2
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:

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 ))

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

## Helpful resources

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - August 2024

Check out the August 2024 Power BI update to learn about new features.

#### Fabric Community Update - August 2024

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

Top Solution Authors
Top Kudoed Authors