IF formula with dates

[NvdV1986]

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?

[rohit1991 · Accepted solution]

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]

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()
)

[rohit1991 · Accepted solution]

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 : 

[NvdV1986 · Accepted solution]

Great it works!!!! Thanks!