Accumulate MTD Sales up to a selected date

[tracyng0905]

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

[CNENFRNL · Accepted solution]

A tricky game of manipulation of context,

[tracyng0905]

Hi @CNENFRNL ,
Your solution works!
It is quite interesting for the __dt part. Let me check it!

Thanks a lot!

Tracy

[tracyng0905]

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

[tracyng0905]

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

[tamerj1]

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
    )