Forum Discussion

jayjay0306's avatar
jayjay0306
Helper III
5 years ago
Solved

Measure: Count weekdays?

Hey folks,

I have a small challange trying to make a measure (no access to calculated columns!), which sum the number of weekdays in the current month.

I have made the following measure:

Measure = SUMX('Time',
VAR Weekend_=IF(ISFILTERED('Time'[Date])&&OR(MAX('Time'[Day Name])="lördag",MAX('Time'[Day Name])="söndag"),TRUE(),FALSE())
RETURN
IF(Weekend_=TRUE(),0,1))

(Note: "Lördag"=Saturday and "Söndag"=Sunday (in swedich))

The result I get is the following:

Which  is fine. However, as you may see, the "Measure" sums ALL days in the current month(=31), where I need to sum the weekdays(=23).

 

In other words, I need to sum the "measure"=1.

 

Any ideas?

 

If you have any bette way to do it, please let me know.

 

thanks,

 

/JayJay0306

Need Help
Tips and Tricks
  • amitchandak's avatar
    amitchandak
    5 years ago

    jayjay0306 , find example formula with two dates

     

    a new measures

    Work Day = COUNTROWS(FILTER(ADDCOLUMNS(CALENDAR(Min(Table[Start Date]),Max(Table[End Date])),"WorkDay", if(WEEKDAY([Date],2) <6,1,0)),[WorkDay] =1))

     

     

    a new column

    Work Day = COUNTROWS(FILTER(ADDCOLUMNS(CALENDAR(Table[Start Date],Table[End Date]),"WorkDay", if(WEEKDAY([Date],2) <6,1,0)),[WorkDay] =1))

     

2 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    5 years ago

    jayjay0306 , find example formula with two dates

     

    a new measures

    Work Day = COUNTROWS(FILTER(ADDCOLUMNS(CALENDAR(Min(Table[Start Date]),Max(Table[End Date])),"WorkDay", if(WEEKDAY([Date],2) <6,1,0)),[WorkDay] =1))

     

     

    a new column

    Work Day = COUNTROWS(FILTER(ADDCOLUMNS(CALENDAR(Table[Start Date],Table[End Date]),"WorkDay", if(WEEKDAY([Date],2) <6,1,0)),[WorkDay] =1))

     

  • Jihwan_Kim's avatar
    Jihwan_Kim
    Super User
    5 years ago

    Hi, jayjay0306 

     

    Please try the below.

     

     

    Measure fix =
    VAR weekend =
    SUMMARIZE (
    FILTER (
    SUMMARIZE ( 'Time', 'Time'[Date], 'Time'[Day Name] ),
    OR ( 'Time'[Day Name] = "Saturday", 'Time'[Day Name] = "Sunday" )
    ),
    'Time'[Date]
    )
    VAR currents =
    VALUES ( 'Time'[Date] )
    RETURN
    COALESCE ( COUNTROWS ( EXCEPT ( currents, weekend ) ), 0 )
     

    Hi, My name is Jihwan Kim.

    If this post helps, then please consider accept it as the solution to help other members find it faster.