Counting of working days

[yugofukuda]

Hi, 

I would like to put # of working days as below but I don't know how to do that.

For example, in the case of sep of 2019, there were only 18 working days but I would like to put 22 on the final working day.(other months as well). I would be appreciated if you could help me.

Thank you!!

[mahoneypat · Accepted solution]

Here is the SUMX version of it to add up the values in your IsWorkingDay column.

WD 22 b =
VAR thisdate = 'Date'[Date]
VAR thismonth = 'Date'[Month]
VAR thisyear = 'Date'[Year]
VAR wdafterthisdate =
SUMX (
FILTER (
'Date',
'Date'[Date] > thisdate
&& NOT ( WEEKDAY ( 'Date'[Date] ) IN { 1, 7 } )
&& 'Date'[Month] = thismonth
&& 'Date'[Year] = thisyear
),
'Date'[IsWorkingDay]
)
RETURN
IF ( 'Date'[IsWorkingDay] = 1, 22 - wdafterthisdate )

If this works for you, please mark it as the solution.  Kudos are appreciated too.  Please let me know if not.

Regards,

Pat

[Greg_Deckler]

@yugofukuda - First, this might help https://community.powerbi.com/t5/Quick-Measures-Gallery/Net-Work-Days/m-p/367362#M109

But, more specifically to your case, maybe 

Column =

  VAR __Calc = <some calculation>

RETURN

  IF([Date] = EOMONTH([Date],0),22,__Calc)

[mahoneypat]

Can you just add 4 to your WorkingDayNumber column?  If the number to add depends on the month, you could calculate that as a variable in your expression, and then add that variable.  What is the logic to add 4?

If this works for you, please mark it as the solution.  Kudos are appreciated too.  Please let me know if not.

Regards,

Pat

[yugofukuda]

@mahoneypat 

Thank you for your help! the number to add depends on the month (because number of working  days depends on month) so i don't know how to resolve this matter...

[Greg_Deckler]

@yugofukuda - 

Perhaps get a little more elaborate with the return statement:

Column =

  VAR __Calc = <some calculation>

RETURN

  IF([Date] <> EOMONTH([Date],0),

    __Calc,

    SWITCH(MONTH([Date]),

      1,23,

      2,20,

      3,23,

      4,22,

      5,23,

      6,22,

      7,23,

      8,23,

      9,22,

      10,23,

      11,22,

      12,23

    )

  )

[yugofukuda]

@Greg_Deckler 

Thanks !! but it didn't work. I would like to consider holidays as well.

[Greg_Deckler]

@yugofukuda - Also, Net Work Days - https://community.powerbi.com/t5/Quick-Measures-Gallery/Net-Work-Days/m-p/367362#M109

Was specifically designed to do this and there is a version that accounts for holidays.

[Greg_Deckler]

@yugofukuda - Let's back up because it seems like everyone is just spinning their wheels on this. @yugofukuda - Please be very specific with what you are looking to achieve. How are you arriving at your working days, specifically? How do you end up with 22 for September? What are your values for the other months? Are all months the same every year? Post sample data as text and also post the expected results of that sample data and explain why they should be what they are.

Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

The most important parts are:
1. Sample data as text, use the table tool in the editing bar
2. Expected output from sample data
3. Explanation in words of how to get from 1. to 2.

[yugofukuda]

@Greg_Deckler 

Thank you for your help!! 

I resolved the problem!

[mahoneypat]

@yugofukuda  I believe this expression gets the desired result (assuming you have a Month and Year column in your Date table).

WD 22 = var thisdate = 'Date'[Date]

var thismonth = 'Date'[Month]

var thisyear = 'Date'[Year]

var wdafterthisdate = COUNTROWS(FILTER('Date', 'Date'[Date]>thisdate && NOT(WEEKDAY('Date'[Date]) in {1,7}) && 'Date'[Month] = thismonth && 'Date'[Year]=thisyear))

return IF(NOT(WEEKDAY('Date'[Date]) in {1,7}), 22- wdafterthisdate)

If this works for you, please mark it as the solution.  Kudos are appreciated too.  Please let me know if not.

Regards,

Pat

[yugofukuda]

@mahoneypat 

Thank you very much for your quick response!!It's very near to what I want to do!!

But there is one problem. What I would like to do is to eliminate holidays. How can I eliminate holidays by using your expression?

[mahoneypat · Accepted solution]

Here is the SUMX version of it to add up the values in your IsWorkingDay column.

WD 22 b =
VAR thisdate = 'Date'[Date]
VAR thismonth = 'Date'[Month]
VAR thisyear = 'Date'[Year]
VAR wdafterthisdate =
SUMX (
FILTER (
'Date',
'Date'[Date] > thisdate
&& NOT ( WEEKDAY ( 'Date'[Date] ) IN { 1, 7 } )
&& 'Date'[Month] = thismonth
&& 'Date'[Year] = thisyear
),
'Date'[IsWorkingDay]
)
RETURN
IF ( 'Date'[IsWorkingDay] = 1, 22 - wdafterthisdate )

If this works for you, please mark it as the solution.  Kudos are appreciated too.  Please let me know if not.

Regards,

Pat

[yugofukuda]

@mahoneypat 

Sorry, please help me. When the column is blank, I would like to use previous data as below. Could you tell me how to create the data?? Thank you very much!!

[mahoneypat · Accepted solution]

I saw that and added the IF to address it.  Just remove it and use this as the return

RETURN
22 - wdafterthisdate

If this works for you, please mark it as the solution.  Kudos are appreciated too.  Please let me know if not.

Regards,

Pat

[yugofukuda]

@mahoneypat 

Thank you very much!!!!!!!

[yugofukuda]

@mahoneypat 

Thank you very much for your support!!
It worked well!!

[Fowmy]

@yugofukuda 

Does your rightmost column exist or are trying to create and include the "22" at the end as you explained?
Provide data in Excel or Text format.

________________________

Did I answer your question? Mark this post as a solution, this will help others!.

Click on the Thumbs-Up icon if you like this reply 🙂

YouTube, LinkedIn

[yugofukuda]

@Fowmy 

Thank you for your reply!! sorry, i can´t share my data.
It doesn´t exsit the column yet and i´m trying to creat that.

[lbendlin]

Not sure why you would want to do that, but nothing is stopping you from ignoring the actual result and replacing it with a constant.

What are you trying to achieve?

[yugofukuda]

@lbendlin 

Thank you for your reply. 
I would like to forecast the result with accuracy and to align the 22 on the final working day is very important to do that because for example, % of change is different on final working day and other working days. 

The graph i would like to make is as below.