Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Exclude weekends from Date Measure

Hi,   I use the following measure to calculate the dates between two different date values: days = DATEDIFF('SALES'[OrderDate], 'SALES'[OrderCompleteDate],DAY), but I want to exclude the weekends...
Need Help
  • v-janeyg-msft's avatar
    v-janeyg-msft
    4 years ago

    Hi, Anonymous 

     

    I create a simple sample, you can refer to:

    First you need to create a calendar table and a isweekend column.

    Then you can create a measure to calculate datediff using the calendar table data.

    Like this:

    Table = CALENDARAUTO()
    IsWeekend = if(WEEKDAY([Date])=1||WEEKDAY([Date])=7,1)
    DaysDiff =
COUNTROWS (
    FILTER (
        ALL ( 'Table' ),
        [Date] >= SELECTEDVALUE ( Table1[OrderDate] )
            && [Date] <= SELECTEDVALUE ( Table1[OrderCompleteDate] )
            && [IsWeekend] <> 1
    )
)

    Pbix file is below.

     

    Did I answer your question ? Please mark my reply as solution. Thank you very much.
    If not, please feel free to ask me.

     

    Best Regards,
    Community Support Team _ Janey

v-janeyg-msft's avatar
v-janeyg-msft
Community Support
4 years ago

Hi, Anonymous 

 

I create a simple sample, you can refer to:

First you need to create a calendar table and a isweekend column.

Then you can create a measure to calculate datediff using the calendar table data.

Like this:

Table = CALENDARAUTO()
IsWeekend = if(WEEKDAY([Date])=1||WEEKDAY([Date])=7,1)
DaysDiff =
COUNTROWS (
    FILTER (
        ALL ( 'Table' ),
        [Date] >= SELECTEDVALUE ( Table1[OrderDate] )
            && [Date] <= SELECTEDVALUE ( Table1[OrderCompleteDate] )
            && [IsWeekend] <> 1
    )
)

Pbix file is below.

 

Did I answer your question ? Please mark my reply as solution. Thank you very much.
If not, please feel free to ask me.

 

Best Regards,
Community Support Team _ Janey

Anonymous's avatar
Anonymous
Not applicable
4 years ago

v-janeyg-msft Thanks! And how can I calculate the average days? For example if I add an order number to it and I want to calculate for all the order numbers the average days it takes to complete an order. Thanks

  • v-janeyg-msft's avatar
    v-janeyg-msft
    Community Support
    4 years ago

    Anonymous 

     

    Add a measure.

     

    Measure = 
AVERAGEX( ADDCOLUMNS('Table1',"DaysDiff",
COUNTROWS (
    FILTER (
        ALL ( 'Table' ),
        [Date] >= EARLIER( Table1[OrderDate] )
            && [Date] <= EARLIER ( Table1[OrderCompleteDate] )
            && [IsWeekend] <> 1
    )
)),[DaysDiff])

     

     

    Best Regards,
    Community Support Team _ Janey