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
Memorable Member
Memorable Member

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
Memorable Member
Memorable Member

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

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

Helpful resources

Announcements
May 23 2022 epsiode 5 without aka link.jpg

The Power BI Community Show

Welcome to the Power BI Community Show! Jeroen ter Heerdt talks about the importance of Data Modeling.

charticulator_carousel_with_text (1).png

Charticulator Design Challenge

Put your data visualization and design skills to the test! This exciting challenge is happening now through June10th!

Power BI Dev Camp Session 22 without aka link and time 768x460.jpg

Check it Out!

Watch Session 22 Ted's Dev Camp along with past sessions!

Power BI Release May 2022 768x460.png

Check it out!

Click here to read more about the May 2022 updates!

Top Kudoed Authors