Forum Discussion

jalaomar's avatar
jalaomar
Helper IV
4 years ago

diff % between two months

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 b...
Need Help
Ashish_Mathur's avatar
Ashish_Mathur
Super User
4 years ago

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's avatar
jalaomar
Helper IV
4 years ago

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's avatar
    Ashish_Mathur
    Super User
    4 years ago

    Hi,

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

    • jalaomar's avatar
      jalaomar
      Helper IV
      4 years ago

      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's avatar
        Ashish_Mathur
        Super User
        4 years ago

        Hi,

        Share the download link of your PBI file.