Date duration and conditions

[jalaomar]

Hello,

I have two column in my table, Actual start date & Schedule start date 

I would like to combine these 2 column into 1 column based on a condition. IF there is an actual date display that one else Schedule start date.

Once I have that column I would then like to measure the duration of that date compared to today's date.

ProjectID	ActualStart	ScheduleStart	DateValue	Duration
A	2022-10-14	2022-10-10	2022-10-14	4
B	null	2022-09-23	2022-09-23	25

anyone who can support?

BR

J

[DataInsights · Accepted solution]

@jalaomar,

Try these calculated columns:

DateValue = COALESCE ( Table1[ActualStart], Table1[ScheduleStart] )

Duration = DATEDIFF ( Table1[DateValue], TODAY(), DAY )

[jalaomar]

Thanks @DataInsights ! It works

Have another question, now that I have the Duration, I added another measure to give some value based on duration days 

if duration >0 && duration <=7, 1,0 etc.

I would like to count number if rows with the value 1 if column Project ID is blank.

so below we have some rows with value 3 and I would like to count number of rows with value 3 where the Project ID rows is blank.

Hope the question makes sense.

Thanks!

[DataInsights]

@jalaomar,

You could try a measure like this:

Count Measure = 
CALCULATE (
    COUNTROWS ( Table1 ),
    Table1[Fulfillment] = 1,
    ISBLANK ( Table1[ProjectID] )
)

Make sure ProjectID is actually blank and not an empty string.

[jalaomar]

Hi, It didn't exatcly work. I did change the number to match my Fulfillment measure.

[DataInsights]

@jalaomar,

I created calculated columns for Duration and Fulfillment, whereas you are using measures. To filter in a CALCULATE expression as I did in line 4 of [Count Measure], you have to use a column. Another advantage to using calculated columns for Duration and Fulfillment is that you can use them in a slicer/filter.