Forum Discussion
Calculate Monthly Difference DAX
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.
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 PreamountDifference = 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.
2 Replies
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 PreamountDifference = 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.