Forum Discussion
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....
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 :
Great it works!!!! Thanks!
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 :
Great it works!!!! Thanks!