Need help with Calendar Function

[Anonymous]

Hi,

I need to calculate the network days between the create date of an event and the schedule date. The caveat is that the scheduled date is not always populated.

I created a calculated column with the following var statement but I am receiving an error message that my start or end date in the calendar function can not be a blank value.

Any help would be greatly appreciated. I was thinking maybe a possible IF statement but when I do I received another error message.

Network Days =

Var TBL_Date =CALENDAR( [Date/Time Opened], [Scheduled Date Time].[Date])

Var TBL_FinalDate =

ADDCOLUMNS(TBL_Date,"WorkingDay",IF(WEEKDAY([Date],2)>=6,0,1)

)

Return

SUMX(TBL_FinalDate, IF([WorkingDay]=1,1,0)

)

[v-lid-msft]

Hi @Anonymous ,

You formula works fine on my side, We create a simple version and get the same result:

Network Days = 
VAR TBL_Date =
    CALENDAR ( [Date/Time Opened], [Scheduled Date Time].[Date] )
RETURN
    SUMX ( TBL_Date, IF ( WEEKDAY ( [Date], 2 ) < 6, 1, 0 ) )

If it doesn't meet your requirement, kindly share your sample data and expected result to me if you don't have any Confidential Information. Please upload your files to One Drive and share the link here.

BTW, pbix as attached.

Best regards,

Community Support Team _ Dong Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Hi, 

Thank you for replying back. My problem is that I dont always have an Scheduled date.

[v-lid-msft]

Hi @Anonymous ,

We can try to create measure using following formula if the scheduled is blank

Network Days =
VAR TBL_Date =
    CALENDAR (
        SELECTEDVALUE ( 'Table'[Date/Time Opened] ),
        IF (
            ISBLANK ( SELECTEDVALUE ( 'Table'[Scheduled Date Time] ) ),
            TODAY (),
            SELECTEDVALUE ( 'Table'[Scheduled Date Time].[Date] )
        )
    )
RETURN
    SUMX ( TBL_Date, IF ( WEEKDAY ( [Date], 2 ) < 6, 1, 0 ) )

Or create the calculated column using following measure,

Network Days =
VAR TBL_Date =
    CALENDAR (
        [Date/Time Opened],
        IF ( ISBLANK ( [Scheduled Date Time] ), TODAY (), [Scheduled Date Time].[Date] )
    )
RETURN
    SUMX ( TBL_Date, IF ( WEEKDAY ( [Date], 2 ) < 6, 1, 0 ) )

Best regards,

Community Support Team _ Dong Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[v-lid-msft]

Hi @Anonymous ,

Could you please share your expected result if there are no scheduled date? For example Opened date is 2019/1/1  and scheduled date is blank?

Best regards,

Community Support Team _ Dong Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.