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....
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 : 

     

     

  • NvdV1986's avatar
    NvdV1986
    11 months ago

    Great it works!!!! Thanks!

     

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