Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago

date evaluation

Hi everyone I have a problem with a date evaluation.   Starting position: List 1: Calendar Date Working day 01.01.2020 True 02.01.2020 True 03.01.2020 True 04.01.2020 True ...
Need Help
Anonymous's avatar
Anonymous
Not applicable
5 years ago
hi Greg_Deckler 
wow, thanks for the quick answer!
sorry i forgot to explain this part:
the values are total and must be distributed over the individual working days
That is the reason that on 01.01.2020 only a third of A
i have applied your measure but its empty, what did i do wrong?
 
Greg_Deckler's avatar
Greg_Deckler
Community Champion
5 years ago

Anonymous My bad, should have been:

Measure = 
  VAR __Date = MAX('Calendar'[Date])
RETURN
  SUMX(FILTER('Table',[Start]>=__Date && __Date<=[End]),[Value])
  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    hi Greg_Deckler 

    thanks again for the answer
    unfortunately the result is not correct
    the value is a total value and i have to know the values of the individual days, so the value should be divided by the number of working days

    so the results have to be:

    Date            Value   explanation of the calculation

    01.01.2020  16.66   (The Value of A divided by 3 because its 3 Working Days): 50/3 = 16.66

    02.01.2020  26.66   (A divided by 3 and C divided by 3):                                     50/3 + 30/3 = 26.66

    03.01.2020  56.66   (A divided by 3 and B divided by 2 and C divided by 3):      50/3 + 60/2 + 30/3 = 56.66

    04.01.2020  50        (B divided by 2 and C divided by 3 and D divided by 1):      60/2 + 30/3 + 10/1 = 50

    05.01.2020  0          (No Working Day)

    06.01.2020  0          (No Working Day)