Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Grow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.

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
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.