Need DAX formula help (month over month)

[TicaTorch1]

Hi All
Can anyone help me create a measure for "month over month % change"? I have all the $ in a column as "Claims [transaction amount]". I have a dates map column too and have them linked. I tried =sum(claims[transaction amount])-previousmonth([claim date])/[transaction amount]).

Desired result: current month transaction amount - previous month transaction amount / current month transaction amount.

 

[TicaTorch1]

So I got the desired result for current month $ - prev month $, using =sum(claims[trans Amount])-Calculate(Sum(Claims[trans amount]),Parallelperiod(Claims[date],-1,month)).

But how can I factor in "divide by previous month" in this measure?

 

[anvg]

Hi
Try

=sum(claims[trans Amount]) / Calculate(Sum(Claims[trans amount]),Parallelperiod(Claims[date],-1,month)) - 1

Regards,

 

[TicaTorch1]

Thank you.. it gives me a valid calculation on the powerpivot, but on my (slicer) pivot table, its returns a #NUM in my columns. Would I need to format that somehow.

 

[TicaTorch1]

On my pivot table:
Months in Column
Total Transactions $ in Values
MoM% (that you provided) in Values. It gives me a % of the grand total of transactions but doesn't break out to months.

 

[bigck2]

Not sure if this would help, but I have made many MoM% fields. The way I did it was to create mutliple caluclated measures and build up the MoM% change measure.

Total_Claims:=SUM( claims[transaction amount] )

PM_Total_Claims:=CALCULATE( Total_Claims, DatesAdd( Dim_Calendar[Date], -1, months ) )

MOM_Change:= [Total Claims] - [PM_Total_Claims]

MOM_%_Change:= IFERROR( MOM_Change / PM_Total_Claims, BLANK())




I may have butchered the syntax on the DatesAdd function.

 

[TicaTorch1]

Thank you so much, this help greatly! Although, I also needed a "current month total claims" too. My % is not coming out right because it's calculating from the TOTAL CLAIMS (which hold prior months). Can you give me a formula for "current month" also taking in account that if TODAY is used, it will reflect the system clock. So for example, this is what I need.

Total Claims (has prior months and future month will be added)
Current month (sept) - if TODAY is used, it will look for October that doesn't exist yet and return "zero"
Prior month (aug)

 

[bigck2]

I hear what you are saying but have never implemented anything myself for Current_Month_Total. I have always may the measure like Total_Claims and them either used a filter on the PivotTable or the row/column context to limit the PivotTable to not show future months.

You may find this website helpful: http://www.daxpatterns.com/time-patterns/