topic Calculate sum but report it as the month ahead in DAX Commands and Tips

Calculate sum but report it as the month ahead

RES — Mon, 21 Apr 2025 20:40:55 GMT

Report Month	Value	Incident
1/1/2025	5	2/20/2025
1/1/2025	10	2/5/2025
1/1/2025	5
2/1/2025	5	3/10/2025
2/1/2025	50
2/1/2025	5

So I want to put the Report month on the X axis of a bar chart but my observations fall off after an incident occurs, so I did some manipulating and have the incident date reported on the most recent report date MAX(REPORT DATE) for that observation. Hence why Indicent is one month ahead of report date. So thats why there are empty incident dates because those observsations carry onto the next report month.

How would I put report month on the X Axis for a bar chart that sums the Values and reports it as the next month. For instance

The X axis is February and it shows a total of 15, then for March it totals 5.

Could this be a measure? I want to pair metric with another metric but that metric is within the report month so it reports properly along the X Axis. I do not want to make a column that shifts the date one month becuase then the other metric will be not reflective. Basically Im trading off one metric being either behind or ahead of the shown X axis description if I keep both metrics on the same graph - which I would much much rather prefer.

Re: Calculate sum but report it as the month ahead

Irwan — Mon, 21 Apr 2025 22:44:08 GMT

hello RES

something like this?

Hope this will help.

Thank you.

Re: Calculate sum but report it as the month ahead

v-dineshya — Tue, 22 Apr 2025 06:39:30 GMT

Hi RES ,

Thank you for reaching out to the Microsoft Community Forum.

The cleanest way to handle this without altering your data model or creating a new column is to use a measure that shifts the value aggregation one month ahead for display purposes.

DAX measure that Sums Value from previous month’s Report Month and Displays that value in the current month on the X-axis

Shifted Value (Next Month) =
VAR CurrentMonth = MAX('Date'[Date])
VAR PrevMonth = EOMONTH(CurrentMonth, -1)
RETURN
CALCULATE(
SUM('YourTable'[Value]),
'YourTable'[Report Month] = PrevMonth
)
If your 'Report Month' is a first-of-month date like 1/1/2025, this will work perfectly with EOMONTH shifting the month, even though it's technically the end of the previous month.

Note: Put 'Date'[Month] or 'Date'[Date] on the X-axis. Use this new shifted measure and your existing metric side-by-side.
one bar shows the shifted metric and the other bar shows the regular one, but both stay aligned to the same X-axis.

If my response has resolved your query, please mark it as the Accepted Solution to assist others. Additionally, a 'Kudos' would be appreciated if you found my response helpful.

Thank you

Re: Calculate sum but report it as the month ahead

RES — Tue, 22 Apr 2025 14:55:35 GMT

One owuld think this works but nothing is being calculated. Yes ReportMonth is the 1st of every month.

VAR x = MAX('Calendar'[Date])

VAR y = EOMONTH(x, -1)

RETURN

CALCULATE(

SUM(Sheet1[Value]),

Sheet1[ReportMonth] = y

)

Re: Calculate sum but report it as the month ahead

johnt75 — Tue, 22 Apr 2025 15:12:43 GMT

Make sure that 'Calendar'[Date] is used as the relationship to Sheet1[Report Month], and use a column from the Calendar table on the x-axis, e.g. 'Calendar'[Year Month]. Then you can create a measure like

Shifted Value = CALCULATE ( SUM ( Sheet1[Value] ), TREATAS ( DATEADD ( 'Calendar'[Date], 1, MONTH ), Sheet1[Incident] ) )

Re: Calculate sum but report it as the month ahead

RES — Tue, 22 Apr 2025 15:23:57 GMT

This gives ReportMonth of Feb 25 = 5, when it should be 15.

Re: Calculate sum but report it as the month ahead

johnt75 — Tue, 22 Apr 2025 15:30:49 GMT

You don't need the DATEADD

Shifted Value = CALCULATE ( SUM ( Sheet1[Value] ), TREATAS ( 'Calendar'[Date], Sheet1[Incident] ) )

Re: Calculate sum but report it as the month ahead

RES — Tue, 22 Apr 2025 15:38:29 GMT

A single value for column 'Date' in table 'Calendar' cannot be determined. This can happen when a measure formula refers to a column that contains many values without specifying an aggregation such as min, max, count, or sum to get a single result.

I tried using Max(Cal[Date]) and got this error: The TREATAS function expects a table expression for argument '1', but a string or numeric expression was used.

Re: Calculate sum but report it as the month ahead

RES — Tue, 22 Apr 2025 19:59:11 GMT

This ended up working for the shifted variable, dont think you need the not is blank but it works. No relationship between Calendar table and Sheet1 table, but Calendar[date] on X axis.

Back Shifted =

VAR x = MAX('Calendar'[Date])

VAR y = EOMONTH(x,-2)+1

RETURN

CALCULATE(

SUM(Sheet1[Value]),

Sheet1[ReportMonth] = y, NOT(ISBLANK(Sheet1[Incident])

)

For the nonshifted variable, I had to add this to the code.

CALCULATE(sum(Sheet1[Value]), FILTER(Sheet1,Sheet1[ReportMonth]=MIN('Calendar'[Date])))