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 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)

 

)

Need Help

4 Replies

  • v-lid-msft's avatar
    v-lid-msft
    Community Support
    6 years ago

    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'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
        Community Support
        6 years ago

        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.