Forum Discussion
datediff between two columns, with a filter applicable
I have the following data:
Name | Weekly hours | Start Date | End Date | Weeks worked | Hours due to work |
Theresa May | 37 | 06/04/2020 |
| 39 | 1443 |
Boris Johnson | 22 | 01/06/2020 | 31/07/2020 | 9 | 198 |
Keir Starmer | 40 | 01/04/2020 |
| 39 | 1443 |
Formulas:
weeks worked = DATEDIFF([Start Date],IF[End Date],[End Date],TODAY()),WEEK)
hours due to work = CALCULATE(SUM([weeks worked])*SUM([Weekly Hours]))
I am keen for the “Hours due to work” to work on a date filter too, so if I want to know how many hours An or Keir were meant to work let’s say in the month of December, it would show up as 4 weeks worked with 37 hours each week = 148 hours
Hi Valerie78 ,
Here are the steps you can follow：
1. Create a calendar table
Date = CALENDARAUTO()2. Create measure.
weeks worked = var _startdate=MIN('Date'[Date]) var _enddate=MAX('Date'[Date]) return DATEDIFF(_startdate,_enddate,WEEK)hours due to work = CALCULATE([weeks worked]*SUM([Weekly Hours]))3. Put the date of the calendar table into the slicer
4. Result.
You can downloaded PBIX file from here.
Best Regards,
Liu Yang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
4 Replies
- what is a week, which day does it start, which day does it end, is it mapped to years, quarters or months in any way?
- do you plan to exclude weekends and holidays? If so, which days are weekend? Which regional holidays do you want to consider?
- can "weekly hours" safely be broken down into "daily hours" or do you have special provisions like half days , bridge days etc?
Your best bet is to have a fully formed calendar table that clearly indicates which days are working days (better yet indicate the working hours for each day.
Hi
I do have a seperate calendar table which is managed with a relationship connection that shows working days vs non working days.
Valerie78 , Try measures like
measure =
sumx(Table, Datediff([Start Date], coalesce([End Date], today()), Week) * [Weekly hours])or based on selected date
measure =
var _max = maxx(allselected('Date'), 'Date'[Date])
return
sumx(Table, Datediff([Start Date], coalesce([End Date], _max), Week) * [Weekly hours])Better to have daily rate and multiple it by workdays
Hi Valerie78 ,
Here are the steps you can follow：
1. Create a calendar table
Date = CALENDARAUTO()2. Create measure.
weeks worked = var _startdate=MIN('Date'[Date]) var _enddate=MAX('Date'[Date]) return DATEDIFF(_startdate,_enddate,WEEK)hours due to work = CALCULATE([weeks worked]*SUM([Weekly Hours]))3. Put the date of the calendar table into the slicer
4. Result.
You can downloaded PBIX file from here.
Best Regards,
Liu Yang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
