Dax help

[Avinash_007]

Hi, power bi expert.

Need a help in calculating a column for 5 different flag as below.

I have a column of date range from year1960 to year2030 and from it i want to calculate a column which will have 5 falgs for 1-current month,2-previous month,3-previous-1 and 4- last year and the 5- No.

Where current will be the current month and then previous-1, previous -2 and previous -3 and the rest will be NO.

Suppose this is Feb 2023 so current flag will be Feb, previous will be Jan2023 , previous -1 will be dec2022 and last year will be nov 2022 and the rest should be no.

Please help me to calculate this using dax.l

[Greg_Deckler · Accepted solution]

@Avinash_007 Not sure I am 100% on the requirements but try this. PBIX is attached below signature.

Column = 
    VAR __Date = [Date]
    VAR __Today = TODAY()
    VAR __CurrentEOM = EOMONTH(__Today,0)
    VAR __CurrentBOM = DATE(YEAR(__CurrentEOM),MONTH(__CurrentEOM),1)
    VAR __PreviousEOM = EOMONTH(__Today,-1)
    VAR __PreviousBOM = DATE(YEAR(__PreviousEOM),MONTH(__PreviousEOM),1)
    VAR __PreviousEOM1 = EOMONTH(__Today,-2)
    VAR __PreviousBOM1 = DATE(YEAR(__PreviousEOM1),MONTH(__PreviousEOM1),1)
    VAR __PreviousEOM2 = EOMONTH(__Today,-3)
    VAR __PreviousBOM2 = DATE(YEAR(__PreviousEOM2),MONTH(__PreviousEOM2),1)
    VAR __Result = 
        SWITCH(TRUE(),
            __Date <= __CurrentEOM && __Date >= __CurrentBOM,"Current Month",
            __Date <= __PreviousEOM && __Date >= __PreviousBOM,"Previous Month",
            __Date <= __PreviousEOM1 && __Date >= __PreviousBOM1,"Previous Month -1",
            __Date <= __PreviousEOM2 && __Date >= __PreviousBOM2,"Previous Month -2",
            "NO"
        )
RETURN
    __Result