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
PBI_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

Welcome Super Users.jpg

Super User Season 2

Congratulations, the new Super User Season 2 for 2021 has started!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

Teds Dev Camp Oct. 2021 768x460.jpg

Power BI Dev Camp - October 28th, 2021

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