Calculate Monthly Difference DAX

[Anonymous]

Hello Power Users,

I'm trying to create a measure or calculation for Monthly difference. but not getting exactly what i need. Basically what i'm trying to do get the difference for Amount for each Milestone number & Date. 

I have create a previous month measure like calculate (Sum(Amount), Prevousmonth(Date) that gives me the previous month amount and i'm just subtracting amount-previousmonth amount to the difference. If you observe in the data there is no data for march for 123 in that i would like get the difference Feb to Apr. it should be like 200-400 but in my logic there is no data  for march then it considers as 0 then its give me 200-0. 

Please help. 

[v-zhangti · Accepted solution]

Hi, @Anonymous 

You can try the following methods.

Preamount =
VAR PreDate =
    MAXX (
        FILTER (
            ALL ( 'Table'[Entry_Date], 'Table'[Milestonenumber] ),
            [Entry_Date] < SELECTEDVALUE ( 'Table'[Entry_Date] )
                && [Milestonenumber] = SELECTEDVALUE ( 'Table'[Milestonenumber] )
        ),
        'Table'[Entry_Date]
    )
VAR Preamount =
    CALCULATE (
        SUM ( 'Table'[amount] ),
        FILTER (
            ALL ( 'Table' ),
            [Entry_Date] = PreDate
                && [Milestonenumber] = SELECTEDVALUE ( 'Table'[Milestonenumber] )
        )
    )
RETURN
    Preamount

Difference = IF([Preamount]<>BLANK(),[Preamount]-SUM('Table'[amount]))

Is this the result you expect?

Best Regards,

Community Support Team _Charlotte

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Thank you so much