Forum Discussion

NvdV1986's avatar
NvdV1986
Helper I
11 months ago
Solved

IF formula with dates

I’m trying to calculate revenue in Power BI for the previous month and two months back. I can’t get this to work with previousmonth or any other option, so I started looking into a calculated colunm. It now works this way: 

 
IF( DetailsSage[Z: DAX invoice month] = 5 && DetailsSage[Z: DAX invoice year] = 2025, "Last month",
IF(DetailsSage[Z: DAX invoice month] = 6 && DetailsSage[Z: DAX invoice year] = 2025, "Current month") )

But now I want to automate this so that I don’t have to adjust the formula every month, but I can’t get it to work.
Right now I have:

IF( (DetailsSage[Entry date] = TODAY(), -1, month),"Current month",

IF((DetailsSage[Entry date] = TODAY(), -2, month), "Last month"))

This formula looks at the input date, then it should subtract 1 or 2 months from it, and if it matches, it should return a text. How can I get this working?

Need Help
  • rohit1991's avatar
    rohit1991
    11 months ago

    Hi NvdV1986 

    In Power BI you don’t need to hardcode month numbers, you can make it dynamic with EOMONTH and TODAY(). Try this as a calculated column:

    MonthFlag = 
VAR InvDate = [Entry date]
VAR ThisMonth = EOMONTH(TODAY(), 0)
VAR LastMonth = EOMONTH(TODAY(), -1)
VAR TwoMonthsBack = EOMONTH(TODAY(), -2)
RETURN
    SWITCH (
        TRUE(),
        EOMONTH(InvDate, 0) = ThisMonth,       "Current month",
        EOMONTH(InvDate, 0) = LastMonth,       "Last month",
        EOMONTH(InvDate, 0) = TwoMonthsBack,   "Two months back",
                                               "Other"
    )

     

    Outcome : 

     

     

3 Replies

  • rohit1991's avatar
    rohit1991
    Super User
    11 months ago

    Hi NvdV1986 

    In Power BI you don’t need to hardcode month numbers, you can make it dynamic with EOMONTH and TODAY(). Try this as a calculated column:

    MonthFlag = 
VAR InvDate = [Entry date]
VAR ThisMonth = EOMONTH(TODAY(), 0)
VAR LastMonth = EOMONTH(TODAY(), -1)
VAR TwoMonthsBack = EOMONTH(TODAY(), -2)
RETURN
    SWITCH (
        TRUE(),
        EOMONTH(InvDate, 0) = ThisMonth,       "Current month",
        EOMONTH(InvDate, 0) = LastMonth,       "Last month",
        EOMONTH(InvDate, 0) = TwoMonthsBack,   "Two months back",
                                               "Other"
    )

     

    Outcome : 

     

     

  • Shahid12523's avatar
    Shahid12523
    Community Champion
    11 months ago

    MonthLabel =
    VAR EntryDate = DetailsSage[Entry date]
    VAR ThisMonth = TODAY()
    VAR LastMonth = EOMONTH(ThisMonth, -1)
    VAR TwoMonthsAgo = EOMONTH(ThisMonth, -2)

    RETURN
    SWITCH(
    TRUE(),
    YEAR(EntryDate) = YEAR(ThisMonth) && MONTH(EntryDate) = MONTH(ThisMonth), "Current month",
    YEAR(EntryDate) = YEAR(LastMonth) && MONTH(EntryDate) = MONTH(LastMonth), "Last month",
    YEAR(EntryDate) = YEAR(TwoMonthsAgo) && MONTH(EntryDate) = MONTH(TwoMonthsAgo), "Two months ago",
    BLANK()
    )