cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
ripstaur
Helper III
Helper III

Multiple IF or SWITCH with dates

I have a column containing report dates. I want to create another column that reflects a reporting period, so for instance if the date of the report is after 2/5, it returns "Feb YY" and if any report date is after 1/7 it returns "Jan YY." I understand that I would need to have it parse the latest date first, then nest another "if" statement after it for the earlier reporting period. 

I have tried 

Reporting Period = IF ( [Report Date] > 2/5/YY, "Feb YY", IF ( [Report Date] > 1/5/YY, "Jan "YY", "NA"))

 

and 

 

Reporting Period = SWITCH(TRUE(), [Report Date] > 2/5/YY, "Feb YY", [Report Date] > 1/5/YY, "Jan YY")

 

Both scenarios return a column with Feb YY in every row, unless I switch the dates; whichever date comes first is what ends up being evaluated, and its result returned throughout. 

 

Any ideas? 

            

 

1 ACCEPTED SOLUTION
nandic
Super User
Super User

Hi @ripstaur ,
You should use Date function when referencing dates.

Reporting Period v2 =
IF (
    [Report Date] > DATE ( 202125 ),
    "Feb 21",
    IF ( [Report Date] > DATE ( 202115 )"Jan 21""NA" )
)

Attached demo file. You can make it more dynamic if needed to always compare to 5th day of month. Idea started, you can customize if needed.

 

This is the result:

date period.PNG

 

Best regards,
Nemanja Andic

View solution in original post

2 REPLIES 2
nandic
Super User
Super User

Hi @ripstaur ,
You should use Date function when referencing dates.

Reporting Period v2 =
IF (
    [Report Date] > DATE ( 202125 ),
    "Feb 21",
    IF ( [Report Date] > DATE ( 202115 )"Jan 21""NA" )
)

Attached demo file. You can make it more dynamic if needed to always compare to 5th day of month. Idea started, you can customize if needed.

 

This is the result:

date period.PNG

 

Best regards,
Nemanja Andic

View solution in original post

This is exactly what I needed to know! Thank you so much! 

Helpful resources

Announcements
2022 Release Wave 1 760x460.png

2022 Release Wave 1 Plan

Power Platform release plan for the 2022 release wave 1 describes all new features releasing from April 2022 through September 2022.

Power BI December 2021 Update_carousel 768x460.jpg

Check it Out!

Click here to read more about the December 2021 Updates!

Jan 2022 Dev Camp 768x460 copy.png

Power BI Dev Camp- January 27th, 2022

Mark your calendars and join us for our next Power BI Dev Camp!

Top Solution Authors