Forum Discussion
Resolver ICount work days between two dates but from different months
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
Solution SageHi 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.
dsj8wksnnckkGood 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.