Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
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:
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 =
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!
Solved! Go to Solution.
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!!
@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 ))
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!!
Check out the July 2025 Power BI update to learn about new features.
User | Count |
---|---|
23 | |
9 | |
7 | |
6 | |
6 |
User | Count |
---|---|
28 | |
11 | |
11 | |
10 | |
6 |