Calculate sum but report it as the month ahead

[RES]

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.  

[johnt75]

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] )
)

[RES]

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

[johnt75]

You don't need the DATEADD

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

[RES · Accepted solution]

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])))

[RES]

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.

[v-dineshya]

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

[RES]

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

)

[Irwan]

hello @RES 

something like this?

Hope this will help.

Thank you.