DAX help for last 13 month amount with single data filter

[JulietZhu]

I used the following DAX to calculate last 13 month amount,

Amt(last n months) =
VAR MaxFactDate =
    CALCULATE ( MAX ( Transactions[TransactionDate]), ALL ( 'Date') )
VAR FDate =
    ENDOFMONTH ( 'Date'[Date] )
VAR Edate =
    EDATE ( FDate, -13 )
RETURN
    IF (
MaxFactDate <= MAX ( 'Date'[Date] )
&& MaxFactDate > Edate,
        CALCULATE ( Transactions[Amount_CurrentM] , ALL ( 'Date') )
    )

But , when I choose Sep, June and April, it gives 14 month data. Oct, Aug, July,May March etc give correct 13 months.

Don't know why. Anyboday help it out. Thanks

Dummy data link,

https://1drv.ms/u/s!AlYpYKwSuOKxhFKUw4W_d_r8VIFu

[v-juanli-msft · Accepted solution]

Hi @JulietZhu

Modify your formula with the following

Amt(last n months) = 
VAR MaxFactDate =
    CALCULATE ( MAX ( Transactions[TransactionDate]), ALL ( 'Date') ) 
VAR FDate =
    ENDOFMONTH ( 'Date'[Date] ) 
VAR Edate =
    EDATE ( FDate, -13 ) 
VAR Edate1=IF(DAY(FDate)=30,Edate+1,IF(DAY(FDate)=28,Edate+3,IF(DAY(FDate)=29,Edate+2,Edate)))
RETURN
    IF (
MaxFactDate <= MAX ( 'Date'[Date] )
&& MaxFactDate > Edate1,
        CALCULATE ( Transactions[Amount_CurrentM] , ALL ( 'Date') )
    )

Best Regards

Maggie

[JulietZhu]

@v-juanli-msft, it works. Thanks