Custom columns that return projects that are >=30 days and <= 90 days of In Service Date

[rosamhernandez1]

I have a table that contains (among other columns) the following columns: Project Name, PISD, and ICFISD

The ask is to identify projects where the ICFISD is between 30 days of the PISD and a separate list of projects where the ICFISD is between 90 days of the PISD - we will always have a PISD, we may not always have the ICFISD. 

Project Name	PISD	ICFISD	>=30	<=90
Allen 1	12/31/2025	10/31/2025
Allen 2 (GRR)	10/31/2027	9/3/2027
Allen 3	10/1/2028
Anclote	12/31/2029
Asheville   (fmr. Lake Julian)	3/31/2026	8/15/2025
Atkinson	3/1/2029
Avon Park	12/31/2029
Aynor	12/31/2026
Camp Lejeune 2	4/30/2027
Cliffside	7/1/2029
Coleridge	9/30/2027	3/31/2031
Craggy	3/31/2026	12/1/2025
Dan River	1/1/2029
Elm City	9/30/2025	6/30/2025
Eno	6/30/2030
Frieden	5/31/2025
Half Moon SPS Retrofit	12/31/2028
HF Lee 1	4/1/2028	6/2/2028
HF Lee 2	10/1/2028	4/2/2029
Jumper Creek SPS Retrofit	12/31/2028
Knightdale (Wake)	9/30/2025	5/30/2025
Longtown	2/1/2026	3/31/2026
Maiden Creek	6/30/2027
Monroe	12/31/2024	1/15/2025
Nebo	3/27/2026	3/31/2026
New Hill	12/31/2026	6/30/2026
Oriental	3/1/2029
Powerline	3/1/2027	6/1/2029
Rattler SPS Retrofit	12/31/2028
Riverbend	10/31/2027	5/21/2027
Riverside	9/25/2025	12/31/2022
Sadler	1/1/2029
Spring Hope	4/1/2029
Suwannee	2/28/2025	11/30/2024
Warrenton	12/1/2029
Warsaw	10/31/2024	7/19/2024
Warsaw 2	12/31/2025
Weatherspoon	4/1/2029
Wilkes	12/31/2027	10/1/2027
Williamsboro	6/30/2027	3/31/2031

[Kedar_Pande]

@rosamhernandez1 

Create a calculated column to check if ICFISD is within 30 days before PISD:

30 Days = 
IF(
NOT(ISBLANK([ICFISD])) &&
[PISD] - [ICFISD] >= 0 &&
[PISD] - [ICFISD] <= 30,
"Yes",
BLANK()
)

Create another calculated column to check if ICFISD is within 90 days before PISD:

90 Days = 
IF(
NOT(ISBLANK([ICFISD])) &&
[PISD] - [ICFISD] >= 0 &&
[PISD] - [ICFISD] <= 90,
"Yes",
BLANK()
)

💌If this helped, a Kudos 👍 or Solution mark would be great! 🎉
Cheers,
Kedar
Connect on LinkedIn

[Sahir_Maharaj]

Hello @rosamhernandez1,

You need two custom columns to evaluate the conditions. Can you please try the following:

Is_>=30_Days = 
IF(
    NOT(ISBLANK([ICFISD])) && 
    DATEDIFF([PISD], [ICFISD], DAY) >= 30, 
    "Yes", 
    "No"
)

Is_<=90_Days = 
IF(
    NOT(ISBLANK([ICFISD])) && 
    DATEDIFF([PISD], [ICFISD], DAY) <= 90, 
    "Yes", 
    "No"
)

Hope this helps.

[rosamhernandez1]

@Sahir_Maharaj, thank you. I am a relatively new user to Power Bi, learning on the job, and I am attempting to add custom columns using Power Query; is this for use in that space? I am receiving an error message at the IF. 

[Anonymous · Accepted solution]

Hi, @rosamhernandez1 

You can create custom column in PowerQuery, and try the following expression:

= if [ICFISD] <> null and Duration.Days([PISD] - [ICFISD]) >= 30 then "Yes" else "No"

= if [ICFISD] <> null and Duration.Days([PISD] - [ICFISD]) <= 90 then "Yes" else "No"

Here is my preview:

How to Get Your Question Answered Quickly

Best Regards

Yongkang Hua

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[rosamhernandez1]

@Anonymous, thank you! this works perfectly!