Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago

Need help with Calendar Function

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 co...
Need Help
Anonymous's avatar
Anonymous
Not applicable
6 years ago

Hi, 

 

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

v-lid-msft's avatar
v-lid-msft
Icon for Community Support rankCommunity Support
6 years ago

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.