Offset Month Calculated Column

[ArchStanton]

Hi,

I have a column named 'Month' which just has the dates of the 1st day of each month (see below)

The MAX month as you can see is Sep 2022.

I would like to create a calculated column 'Offset Month' whereby the Max month will always be zero and the previous month -1 and then -2 and so on. The logic sounds very simple but I'm still learning DAX and not quite there yet, can anyone help?

Thanks

[DataInsights · Accepted solution]

@ArchStanton,

Try this calculated column in the table DimDate:

Offset Month = 
VAR vToday =
    TODAY ()
VAR vResult =
    DATEDIFF ( vToday, DimDate[Date], MONTH ) + 1
RETURN
    vResult

[Anonymous]

Hi there,

I am trying to accomplish this same thing, but I am receiving the following error. Any idea why this wouldn't be working for me?

Thanks!

[ArchStanton]

The solution was in DAX so its meant to be used in the Data Model, I can see you're in Query Editor and that uses M language, thats a different environment.

Cancel that and instead create a calculated column using the same code in your table within the datamodel instead.

[Anonymous]

Ah hah. That did the trick. I am still new to Power BI, so still much to learn. Thank you!

[ArchStanton]

No problem

[ArchStanton]

Perfect, thats exactly what I need!

Many thanks!