datediff between two columns, with a filter applicable

[Valerie78]

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

[v-yangliu-msft · Accepted solution]

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.

[amitchandak]

@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

[lbendlin]

- 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.

It's the weekend! Or is it? - Microsoft Power BI Community

[Valerie78]

Hi 

I do have a seperate calendar table which is managed with a relationship connection that shows working days vs non working days.