diff % between two months

[jalaomar]

Hello,

I have a financial table which captures a snapshot of margin% each month and what I would like to do is calculate what was the margin previous moth compared to current month?

so from below screenshot between June and July the forecast Margin% deviation is 0%. 

anyone know how to do the calculation? have tried some calculation but they dont't give the right results.

Many Thanks!

[Ashish_Mathur]

Hi,

Assuming %Forecast Margin is a measure, create a Calendar Table.  In the Calendar Table, write calculated column formulas to extract Year, Month name and Month number.  Sort the Month name by the Month number.  To your visual, drag Year and Month name from the Calendar Table.  Create a relationship (Many to One and Single) from the Date column of your Data Table to the Date column of the Calendar Table.  Write this measure:

Measure = [%Forecast Margin]-calculate([%Forecast Margin],previousmonth(Calendar[Date]))

Hope this helps.

[jalaomar]

Hi, I used a slightly different measure but would you know how to dynamically ensure that the deviation after the first month shows blank instead and not show value for current month as well?

so november and August should be blank in the BM% Dev. Prev. Month same for FM% Dev. Prev. month

BR

J

[Ashish_Mathur]

Hi,

Is your FY from November to October.  Why should the % for Aug be empty?

[jalaomar]

My FY is from November to July. There is still no data for August, will bee seen 1st of Sep. so we will only see data up to previous month 

[Ashish_Mathur]

Hi,

Share the download link of your PBI file.

[Rudy_D]

Hey,

Sorry I was out of the office for days.

Please find my file at this link : https://we.tl/t-CBIgkDFX4q 

The result is just below. Is it what you want ?

Rudy

[Rudy_D]

Hi,

You can create the measure below :

result = sum(% Forecast Margin) - CALCULATE(sum(% Forecast Margin), PREVIOUSMONTH(Snapshot))

And than you put it in your visual.

Rudy

[jalaomar]

The measure didn't work unfortunatley