Caculate value between dates and value without enddate

[AltusTellus]

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':

tb_SchedulesCalendar =

ADDCOLUMNS(

CALENDAR( MIN( tb_Schedules[StartDate] ); MAX( tb_Schedules[EndDate] ) );

"Year Month"; FORMAT( [Date]; "mmm-yy" );

"Year Month Number"; YEAR( [Date] ) * 100 + MONTH( [Date] )

There is NO relationship between the two tables. I would like to create a matrix like this:

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:

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

               )

)

The result in the matrix is that I've only the schedules with the average hours per period of the schedules with an enddate. How has the DAX formula te be changed so I'll also see the values of the schedules without an enddate?

In advance many thanks! And yes, if there is an suggestion for calculating the average hours per week to a month period, you're very welcome.

Gr. Alfred

[MFelix]

Hi @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

[AltusTellus]

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

[MFelix · Accepted 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.

[AltusTellus]

@MFelix THANKS!