Forum Discussion
Help with a Dax Formula
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!!!
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
6 Replies
Hi,
PBI file attached.
Hope this helps.
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:
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 belowHi 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 !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!
