Forum Discussion

AltusTellus's avatar
AltusTellus
Helper III
6 years ago
Solved

Caculate value between dates and value without enddate

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:

AverageHoursEndDateIDStartDate
40 100101-12-2019
4031-05-2020100201-06-2019
36 100301-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:
 
ID201906201907201908201909201910201911201912202001202002202003202004202005202006202007et cetera
1001      4040404040404040 
1002404040404040404040404040   
1003           363636 

 

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
Need Help
  • MFelix's avatar
    MFelix
    6 years ago

    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.

4 Replies

  • MFelix's avatar
    MFelix
    Super User
    6 years ago

    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's avatar
      AltusTellus
      Helper III
      6 years ago

      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:

       

      ID201910201911201912202001
      100240404076

       

      This means that in the matrix the result unfortunately is not like this:

       

      ID201910201911201912202001
      100240404036

       

      Do you have a solution for this problem? Many thanks again.

      • MFelix's avatar
        MFelix
        Super User
        6 years ago

        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.