Forum Discussion

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

Icon for Community Support rank

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
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
  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.
- v-lid-msft
  Community 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.

Related content