Forum Discussion

colin_dickson's avatar
colin_dickson
Frequent Visitor
3 years ago

Month by month variance from previous month

Hi,   I'm fairly new to Power BI.   I have a simple matrix table which has types of service on the rows, months as columns and the some of each as the values, as follows:   Service Jan Feb...
Need Help
Jihwan_Kim's avatar
Jihwan_Kim
Icon for Super User rankSuper User
3 years ago

Hi,

I am not sure how your datamodel looks like, but please check the below picture and the attached pbix file.

 

 

 

 

expected result measure: =
VAR _currentvalue =
    SUM ( Data[Value] )
VAR _prevmonthvalue =
    CALCULATE (
        SUM ( Data[Value] ),
        OFFSET ( -1, ALL ( 'Month' ), ORDERBY ( 'Month'[Month No], ASC ) )
    )
RETURN
    IF (
        NOT ISBLANK ( _prevmonthvalue ) && HASONEVALUE ( Service[Service] ),
        _currentvalue - _prevmonthvalue
    )

 

  • colin_dickson's avatar
    colin_dickson
    Frequent Visitor
    3 years ago

    Thanks for this, i will give it ago.

     

    I haven't got a data model as you sugest, all of my data work is done in the transform section then i'm using the final appended and  merged data as my source data.

     

    Why do we need a month number, I assume I need to add this to my data?

     

    Regards

    Colin

    • Jihwan_Kim's avatar
      Jihwan_Kim
      Icon for Super User rankSuper User
      3 years ago

      Hi,

      Thank you for your message.

      The reason why we need a month number column is that,

       

      - Jan, Feb, and Mar has to be sorted by month number column, not by alphabetical order.

      - we need to let Power BI know that "Feb" minus one is "Jan".

       

      The month number column helps these kinds of sort-order-arrangement and calculations.

       

      I hope this helps.

      Thanks.