Count work days between two dates but from different months

[dsj8wksnnckk]

Hi all,

I've been struggling how to solve the following problem. I have a dataset that looks something like this after Power Query and some DAX filtering:

Beside this table, I have a Date table that has information about whether a day is a work day or not.

I need to count the Leave days for each row BUT if the Leave from and Leave to are from different months, I need the count SEPARATELY for each month.

My final result should be a matrix with drilldown:
Customer 
--- Project
------- Order
-----------Resource

And Months from Date table as columns, and SUM leave as values BUT like I said previously: the days must be correctly put into their month.

Thanks in advance,
J.

[dsj8wksnnckk · Accepted solution]

I have resolved this with Power Query.

1. Created a list of dates using List.Date (created a new row for each date between the From and To dates)
2. Added a new column to check if the date is work day or not
3. Connected with Date table
4. Used COUNTROWS

[Selva-Salimi]

Hi @dsj8wksnnckk ,

It's not clear fo me if you have any relation between this table and date table but lets assume that there is not any. then you can write a measure as follows:

measure working_day :=

var _leave_from = selectedvalue (leave_from)

var _leave_to = selectedvalue (leave_to)

return

calculate (sum('date' [working_day]) , filter ( 'date', Date >= _leave_from && Date <= _leave_to ))

if you add month from Date table to your visual matrix, then all month will be shown in every row. to solve this problem you can write a measure as follows and add it to filter pane of matrix visual and set it to 1 to just show the related months....

measure months_range:=

var start= selectedvalue (leave_from)

var end= selectedvalue(leave_to)

return if ( 'date' [date] >=start &&  'date' [date] <=end , 1 , 0 )

If this post helps, then I would appreciate a thumbs up and mark it as the solution to help the other members find it more quickly. 

[dsj8wksnnckk]

Good morning,

I have started testing this but for the IF function I get an error 
"A single value for column 'Date' in table 'Date Table' cannot be determined."


BTW I used to have a relationship between the first Table and the date table but it's not necessary if it can work without it.

I have managed to create a matrix that will show me the number of leave days but they will be always sorted in either the From or the To month (first or last date). Can't manage to separate the days between two months.