Forum Discussion

ArchStanton's avatar
ArchStanton
Power Participant
3 years ago
Solved

Offset Month Calculated Column

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

Need Help
  • DataInsights's avatar
    DataInsights
    3 years ago

    ArchStanton,

     

    Try this calculated column in the table DimDate:

     

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

     

     

6 Replies

  • DataInsights's avatar
    DataInsights
    Super User
    3 years ago

    ArchStanton,

     

    Try this calculated column in the table DimDate:

     

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

     

     

    • ArchStanton's avatar
      ArchStanton
      Power Participant
      3 years ago

      Perfect, thats exactly what I need!

      Many thanks!

    • Anonymous's avatar
      Anonymous
      Not applicable
      3 years ago

      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's avatar
        ArchStanton
        Power Participant
        3 years ago

        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.