Forum Discussion

esam's avatar
esam
Frequent Visitor
3 years ago
Solved

Month Series

Hi I have date table from 2023 to 2025 i want a column with month series where 36 being december 2025 and 1 being January 2023.

  • hi esam 

    t

    Aha, try like

    Month Series =

    DATEDIFF(DATE(2023, 1, 1), [Date], MONTH)+1

11 Replies

  • You could try a calculated column like

    Month series =
    VAR ReferenceDate = 'Date'[Date]
    VAR PrevMonths =
        CALCULATETABLE (
            VALUES ( 'Date'[Year month] ),
            REMOVEFILTERS ( 'Date' ),
            'Date'[Date] < ReferenceDate
        )
    RETURN
        COUNTROWS ( PrevMonths ) + 1
    
    • esam's avatar
      esam
      Frequent Visitor

      Removefilter with comparison operator is not correct 

      • johnt75's avatar
        johnt75
        Super User

        I think maybe you've missed a close bracket, the REMOVEFILTERS and comparison are separate.

  • hi esam 

    try to add a column like:

    Month Series =

    DATEDIFF([Date], DATE(2023, 1, 1), MONTH)+1

    • esam's avatar
      esam
      Frequent Visitor

      Hi FreemanZ 

      The solution you provided gave something similar but not accurate it is giving me result in negative 

      for jan 2023 it is one than feb 2023 0 and for december 2025 -33 

      • FreemanZ's avatar
        FreemanZ
        Super User

        hi esam 

        t

        Aha, try like

        Month Series =

        DATEDIFF(DATE(2023, 1, 1), [Date], MONTH)+1

  • smpa01's avatar
    smpa01
    Community Champion

    esam not sure what your Date table looks like, I simply Assumed that you have only 1 column table as Calendar[Date]

     

     

    Column =
    RANKX (
        'Calendar',
        DATE ( MONTH ( 'Calendar'[Date] ), YEAR ( 'Calendra'[Date] ), 1 ),
        ,
        ASC,
        DENSE
    )