Multiple IF or SWITCH with dates

Question

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?

nandic · Accepted Answer

Hi&nbsp;ripstaur&nbsp;,You should use Date function when referencing dates.Reporting&nbsp;Period&nbsp;v2&nbsp;=IF&nbsp;(&nbsp;&nbsp;&nbsp;&nbsp;[Report&nbsp;Date]&nbsp;&gt;&nbsp;DATE&nbsp;(&nbsp;2021,&nbsp;2,&nbsp;5&nbsp;),&nbsp;&nbsp;&nbsp;&nbsp;"Feb&nbsp;21",&nbsp;&nbsp;&nbsp;&nbsp;IF&nbsp;(&nbsp;[Report&nbsp;Date]&nbsp;&gt;&nbsp;DATE&nbsp;(&nbsp;2021,&nbsp;1,&nbsp;5&nbsp;),&nbsp;"Jan&nbsp;21",&nbsp;"NA"&nbsp;))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.
&nbsp;
This is the result:

&nbsp;
Best regards,Nemanja Andic

Forum Discussion

Multiple IF or SWITCH with dates

2 Replies

Related content

Runtime Lakehouse Switch between multiple notebooks

Multiple Date Fields

Date Relationships - Multiple Tables, Multiple dates.

CalculationGroups - switch between dates

Alternative to SWITCH ()?