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.
DAX
Help
  • FreemanZ's avatar
    FreemanZ
    3 years ago

    hi esam 

    t

    Aha, try like

    Month Series =

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

johnt75's avatar
johnt75
Icon for Super User rankSuper User
3 years ago

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
3 years ago

Removefilter with comparison operator is not correct 

  • johnt75's avatar
    johnt75
    Icon for Super User rankSuper User
    3 years ago

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

    • esam's avatar
      esam
      Frequent Visitor
      3 years ago

       

      the series generated by this 13,12,....1

      • johnt75's avatar
        johnt75
        Icon for Super User rankSuper User
        3 years ago

        OK, the below works for me

        Month series = 
VAR ReferenceDate = 'Date'[Date]
VAR PrevMonths =
    CALCULATETABLE (
        DISTINCT ( 'Date'[Year month] ),
        REMOVEFILTERS ( 'Date' ),
        'Date'[Date] <= ReferenceDate
    )
RETURN
    COUNTROWS ( PrevMonths )