Month on Month Difference

[dommyw277]

Hi,

I want to have a chart that shows me the difference month on month.
I have a running total per month in the Billing field but even though i can see the monthly difference i would like a field to show on the bar chart.

[Month01]

Month name

Try this code

Measure = 
var _current_month =  MONTH(SELECTEDVALUE('Table'[Date]))
VAR _current_billing = CALCULATE(SELECTEDVALUE('Table'[Billing]),FILTER(ALL('Table'),MONTH('Table'[Date]) = _current_month))
VAR _previous_billing = CALCULATE(SELECTEDVALUE('Table'[Billing]),FILTER(ALL('Table'),MONTH('Table'[Date]) = _current_month - 1))

RETURN
 _current_billing - _previous_billing

[Anonymous]

Hi @dommyw277 

Based on your needs, I have created the following table.

Then you can use the following  measure to calculate the monthly difference:

Measure = 
var _current_month =  MONTH(SELECTEDVALUE('Table'[Date]))
VAR _current_billing = CALCULATE(SELECTEDVALUE('Table'[Billing]),FILTER(ALL('Table'),MONTH('Table'[Date]) = _current_month))
VAR _previous_billing = CALCULATE(SELECTEDVALUE('Table'[Billing]),FILTER(ALL('Table'),MONTH('Table'[Date]) = _current_month - 1))

RETURN
 _current_billing - _previous_billing

 Result:

Best Regards,

Jayleny

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[dommyw277]

Hi, i tried replacing Billling with my table and it says syntax incorrect. I copied what you typed too and it says same? 

[Anonymous · Accepted solution]

Hi @dommyw277 

I have attached the PBIX file at the end of the reply, which you can download and open to check the difference with your own file.

Best Regards,

Jayleny

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Kedar_Pande]

@dommyw277 

Assuming you already have a measure for the running total, let's say it's called Running Total, you can create a new measure for the monthly difference like this:

Monthly Difference = 
VAR CurrentMonthTotal = [Running Total]
VAR PreviousMonthTotal = 
CALCULATE(
[Running Total],
PREVIOUSMONTH('Date'[Date]) 
)
RETURN
CurrentMonthTotal - PreviousMonthTotal

Drag the Month field from your date table to the Axis of the bar chart.
Drag the Monthly Difference measure you created to the Values section of the bar chart.

💌 If this helped, a Kudos 👍 or Solution mark would be great! 🎉
Cheers,
Kedar
Connect on LinkedIn

[dommyw277]

Thanks. I dont have a running total measure, how do i add that first please?  

Its purely bars of costs per month with a total but just added up

[Kedar_Pande]

Please share a simplified version of your PBIX file (in English) without sensitive data. You can upload it to a public cloud service like OneDrive, Google Drive, or Dropbox and share the link. This will help in understanding your data structure and the issue, allowing for more precise guidance.

[saurav_0101]

To display the month-over-month difference as a separate field in a Power BI bar chart, you can create a new measure to calculate the month-on-month (MoM) difference based on your running total. Here’s how:

1. Create a MoM Difference Measure: Use the following DAX formula, which calculates the difference between the current month’s billing and the previous month’s billing:

   DAX

   MoM Difference = [Billing] - CALCULATE([Billing], DATEADD('Date'[Date], -1, MONTH))

   Here:

   • [Billing] is your running total measure.
   • DATEADD shifts the context to the previous month.

2. Add the Measure to Your Chart:

   • Insert a bar chart and place the MoM Difference measure on the Values field.
   • You can also include [Billing] if you want to compare the running total with the MoM difference.

3. Format the Chart (Optional): To make the difference more visible, adjust the data labels or set conditional formatting to highlight positive and negative differences.

This setup will show the month-on-month difference for each bar, giving you a clear view of monthly changes on your chart. 

If this answers your question, please leave a Kudos or mark it as Solution.