Forum Discussion

AltusTellus's avatar
AltusTellus
Icon for Helper III rankHelper 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 column...
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.

MFelix's avatar
MFelix
Icon for Super User rankSuper 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
Icon for Helper III rankHelper 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
    Icon for Super User rankSuper 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.