# Need DAX formula help (month over month)



## TicaTorch1 (Sep 25, 2017)

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 (Sep 25, 2017)

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 (Sep 25, 2017)

Hi
Try


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


Regards,


----------



## TicaTorch1 (Sep 25, 2017)

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 (Sep 25, 2017)

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 (Sep 26, 2017)

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 (Oct 13, 2017)

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 (Oct 13, 2017)

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/


----------

