Forum Discussion

Avinash_007's avatar
Avinash_007
Regular Visitor
3 years ago
Solved

Dax help

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

Need Help
  • Greg_Deckler's avatar
    Greg_Deckler
    3 years ago

    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

1 Reply

  • Greg_Deckler's avatar
    Greg_Deckler
    Community Champion
    3 years ago

    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