Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hi all,
I'm struggeling with a DAX formula for calculating the value of hours between dates in combination with the value of hours from a startdate.
I've two tables:
1. Schedules with columns for start date and end date, schedule ID and average hours (per week actually), named 'Schedules'. Like this:
AverageHours | EndDate | ID | StartDate |
40 | 1001 | 01-12-2019 | |
40 | 31-05-2020 | 1002 | 01-06-2019 |
36 | 1003 | 01-05-2020 |
2. And I've a table with dates, build up like this, named 'SchedulesCalendar':
ID | 201906 | 201907 | 201908 | 201909 | 201910 | 201911 | 201912 | 202001 | 202002 | 202003 | 202004 | 202005 | 202006 | 202007 | et cetera |
1001 | 40 | 40 | 40 | 40 | 40 | 40 | 40 | 40 | |||||||
1002 | 40 | 40 | 40 | 40 | 40 | 40 | 40 | 40 | 40 | 40 | 40 | 40 | |||
1003 | 36 | 36 | 36 |
I've the next measure:
Solved! Go to Solution.
Hi @AltusTellus ,
I just used you measure that you made, you are summing values, depending on the values you have and number of lines you can change the SUM for MIN, MAX or AVERAGE.
Using the MIN it's giving correct result:
WorkingHours_v2 =
VAR MinDate = MIN( 'tb_SchedulesCalendar'[Date] )
VAR MaxDate = MAX( 'tb_SchedulesCalendar'[Date] )
RETURN CALCULATE(MIN('tb_Schedules'[AverageHours]),
FILTER(
tb_Schedules,
tb_Schedules[StartDate] <= MaxDate
&& tb_Schedules[EndDate] >= MinDate || tb_Schedules[StartDate] <= MaxDate
)
)
See PBIX file attach.
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsHi @AltusTellus ,
SImply add the paremeter OR (||^) with only start date:
WorkingHours_v2 =
VAR MinDate =
MIN ( 'tb_SchedulesCalendar'[Date] )
VAR MaxDate =
MAX ( 'tb_SchedulesCalendar'[Date] )
RETURN
CALCULATE (
SUM ( 'tb_Schedules'[AverageHours] );
FILTER (
tb_Schedules;
tb_Schedules[StartDate] <= MaxDate
&& tb_Schedules[EndDate] >= MinDate
|| tb_Schedules[StartDate] <= MaxDate
)
)
Check PBIX attach
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsHi @MFelix ,
Thnx for your reply, but I have another problem after using the OR function like you described. The result is that a following schedule (e.g. the same ID has a follower at the first of January but with 36 as number for [AverageHours), it counts up the original [AvarageHours] with the follower schedule. Like this:
ID | 201910 | 201911 | 201912 | 202001 |
1002 | 40 | 40 | 40 | 76 |
This means that in the matrix the result unfortunately is not like this:
ID | 201910 | 201911 | 201912 | 202001 |
1002 | 40 | 40 | 40 | 36 |
Do you have a solution for this problem? Many thanks again.
Hi @AltusTellus ,
I just used you measure that you made, you are summing values, depending on the values you have and number of lines you can change the SUM for MIN, MAX or AVERAGE.
Using the MIN it's giving correct result:
WorkingHours_v2 =
VAR MinDate = MIN( 'tb_SchedulesCalendar'[Date] )
VAR MaxDate = MAX( 'tb_SchedulesCalendar'[Date] )
RETURN CALCULATE(MIN('tb_Schedules'[AverageHours]),
FILTER(
tb_Schedules,
tb_Schedules[StartDate] <= MaxDate
&& tb_Schedules[EndDate] >= MinDate || tb_Schedules[StartDate] <= MaxDate
)
)
See PBIX file attach.
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsUser | Count |
---|---|
102 | |
90 | |
80 | |
71 | |
70 |
User | Count |
---|---|
114 | |
100 | |
97 | |
72 | |
68 |