Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
colin_dickson
Frequent Visitor

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:

 

ServiceJanFebMar
Service 1

500

800

300
Service 2600900400
Service 37001,000500

 

What I want to be able to to do is convert that so I can see the variance from the previous month i.e. Service 1 from Jan to Feb would be a positive variance of 300 and so on.

 

I've tried all sorts but nothing seems to work.

 

Any advice would be much appreciated.

 

Thanks

 

Colin

5 REPLIES 5
colin_dickson
Frequent Visitor

I fixed that...

 

It is now only showing one column with variances I don't recognise... 😞

 

Regards

 

Colin

colin_dickson
Frequent Visitor

That makes sense...

 

I'm getting the following error when I add that measure?

 

OrderBy column 'Month No.' could not be found in OFFSET's Relation parameter.

 

Regards

 

Colin

Jihwan_Kim
Super User
Super User

Hi,

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

 

Jihwan_Kim_0-1673374793674.png

 

Jihwan_Kim_1-1673375012445.png

 

 

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
    )

 

If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.


Click here and Go to My LinkedIn Page


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

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. 

If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.


Click here and Go to My LinkedIn Page


Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.