Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi All,
I tried to create DAX to calculate the Accumulate MTD Sales up to the seleted date from Date Slicer.
However, it cant work once I have selected a date value.
It works when no Date is selected:
It can't work when a date is selected:
Below 2 DAXs are tried but not work. Anyone has idea how to achieve it WITHOUT closing the interacton between the date and visual?
Cumulative1 = var maxdate = Max('Date'[Date]) var mindate = Date(Year(maxdate),Month(maxdate),1) RETURN CALCULATE(sum(Fact[NetSales]), ALL('Date'), Fact[TxnDate] <= maxdate, Filter(ALL('Date'[Date]),'Date'[Date]<=max(Fact[TxnDate])) ) |
Cumulative2 = var maxdate = Max('Date'[Date]) var mindate = Date(Year(maxdate),Month(maxdate),1) RETURN IF(CALCULATE(max('Fact'[TxnDate]),ALL('Date'[Date]))<= maxdate, CALCULATE(sum(Fact[NetSales]), ALL('Date'), Filter(ALLSELECTED(Fact[TxnDate]),Fact[TxnDate]<= maxdate) ) ) |
Here is the sample pbix: Accumulate MTD.pbix
Many Thanks!
Tracy
Solved! Go to Solution.
A tricky game of manipulation of context,
Thanks to the great efforts by MS engineers to simplify syntax of DAX! Most beginners are SUCCESSFULLY MISLED to think that they could easily master DAX; but it turns out that the intricacy of the most frequently used RANKX() is still way beyond their comprehension! |
DAX is simple, but NOT EASY! |
A tricky game of manipulation of context,
Thanks to the great efforts by MS engineers to simplify syntax of DAX! Most beginners are SUCCESSFULLY MISLED to think that they could easily master DAX; but it turns out that the intricacy of the most frequently used RANKX() is still way beyond their comprehension! |
DAX is simple, but NOT EASY! |
Hi @CNENFRNL ,
Your solution works!
It is quite interesting for the __dt part. Let me check it!
Thanks a lot!
Tracy
Hi @tamerj1 ,
It still return unexpected result when 1/2/2021 is selected:
It is expected to show 3 for 1/1/2021 and 10 for 1/2/2021 when 1/2/2021 is selected. Any other idea?
Regards,
Tracy
Hi @tamerj1 ,
It still return unexpected result when 1/2/2021 is selected:
It is expected to show 3 for 1/1/2021 and 10 for 1/2/2021. Any other idea?
Regards,
Tracy
Hi @tracyng0905
You can use
Cumulative3 =
VAR maxdate =
MAX ( 'Date'[Date] )
VAR mindate =
DATE ( YEAR ( maxdate ), MONTH ( maxdate ), 1 )
RETURN
CALCULATE (
SUM ( Fact[NetSales] ),
REMOVEFILTERS ( 'Date' ),
'Date'[Date] > mindate,
'Date'[Date] <= maxdate
)
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
42 | |
21 | |
21 | |
14 | |
13 |
User | Count |
---|---|
43 | |
39 | |
33 | |
18 | |
17 |