Help with a Dax Formula

[hstgeorge]

I am trying to create a formula that will show the variance month over month of the values in my table:

Month               Jan          Feb         Mar

Expenses          1000         2500        3000

Revenue           1000         2500        3000

Accruals           1000         2500        3000

so I want the column to show Feb - Jan, then mar - Feb and just show the variance from month to month.

Can someone assist me?  Thank you!!!

[ryan_mayu · Accepted solution]

@hstgeorge 

Is the table your raw data format or the table visual?

If it's your raw data,

1. select the first column and unpivot other columns in PQ

2. create a new column

3. change the type to date

4. use DAX to create a column

Column =

var _last=maxx(FILTER('Table','Table'[Month]=EARLIER('Table'[Month])&&'Table'[Custom]=EDATE(EARLIER('Table'[Custom]),-1)),'Table'[Value])

return if (ISBLANK(_last),BLANK(),'Table'[Value]-_last)

pls see the attachment below

[v-aatheeque]

Hi @hstgeorge 

Just wanted to check in to see if the steps related to the DAX to create a formula that will show the variance month over month of the values shared by the @ryan_mayu @hnguy71  @Ashish_Mathur helped solve your issue.

If you’re still facing challenges or need any further clarification, please let us know we’re happy to help!

Looking forward to your update.
Thanks for being part of the Fabric Community !

[v-aatheeque]

Hi @hstgeorge 

Just wanted to follow up to see if the DAX steps shared by @ryan_mayu @hnguy71 @Ashish_Mathur  helped in resolving your issue with calculating the month-over-month variance. We hope the guidance provided brought you closer to your solution.

If you’re still encountering difficulties or if there’s anything unclear in the approach, please feel free to reach out. We’re more than happy to walk through the logic or help you tailor the formula to your specific data model.

Also, if you’ve found an alternative solution or any insights that might help others facing similar challenges, we’d love for you to share your experience with the community!

Looking forward to hearing from you.
Thanks again for being an active part of the Fabric Community!

[v-aatheeque]

Hi @hstgeorge ,

Just checking in to see if the DAX guidance shared by @hnguy71 @ryan_mayu  helped resolve your issue with calculating month-over-month variance.

If you’re still running into challenges or need help adapting the formula to your model, feel free to reach out  we’d be glad to assist further.

And if you’ve discovered a different solution or approach, we’d love for you to share it with the community to help others with similar questions.

Looking forward to your update!!

If we don’t hear back, we may close this thread in line with our community guidelines, but you’re always welcome to post a new query anytime.
Thank you for being part of the Microsoft Fabric Community!

[hnguy71]

Hi @hstgeorge 

You would have to create a series of measures. Start with a base measure to return your total value such as this:

Base Total = SUM('Table'[Value])

 
Then create a measure to find out your previous months' value:

Previous Total = CALCULATE([Base Total], DATEADD('Table'[Period], -1, MONTH))

 
and finally, subtract the two with another measure:

Variance MoM = [Base Total] - [Previous Total]

Hopefully you should get a result similar to this:

[Ashish_Mathur]

Hi,

PBI file attached.

Hope this helps.