date evaluation

[Anonymous]

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
05.01.2020	False
06.01.2020	False

List 2: Construction program

Name	Start	End	Value
A	01.01.2020	03.01.2020	50
B	03.01.2020	05.01.2020	60
C	02.01.2020	06.01.2020	30
D	04.01.2020	06.01.2020	10

Goal:

Date	Value	calculation only example
01.01.2020	16.66	(A/3)
02.01.2020	26.66	(A/3 + C/3)
03.01.2020	56.66	(A/3 + C/3 + B/2)
04.01.2020	50	(B/2 + C/3 + D/1)
05.01.2020	0
06.01.2020	0

What is the best way to do this?

I've tried several ways, but don't understand how best to get the results. The first attempt was with the formula:

List.Dates([VorgangGeplanterAnfangsTermin],Duration.Days(
[VorgangGeplanterEndTermin]-[VorgangGeplanterAnfangsTermin])+1,#duration(1,0,0,0))

Then I expanded the list that resulted from it, but so i get several lines per component, which is not a good idea.
can I also work with the list without expanding it?
and how can i then take the calendar into account in a measure?

Or do I have to work better with a measure:

DATESBETWEEN(<dates>,<start_date>,<end_date>)

Maybe can @ibarrau  help me?
you helped me a lot last time, unfortunately my dashboard has become more complex and this solution is no longer sufficient

Thanks a lot

T

[amitchandak]

@Anonymous , Based on what I got. I have  similar problems

see if this can help

https://www.dropbox.com/s/o2v5ekfiw5057do/Working%20date%20between%20Dates.pbix?dl=0

or

https://www.dropbox.com/s/bqbei7b8qbq5xez/leavebetweendates.pbix?dl=0

[Greg_Deckler]

@Anonymous Seems like:

Measure = 
  VAR __Date = MAX('Calendar'[Date])
RETURN
  SUMX(FILTER('Table',[Start]>=__Date && [End]<=__Date),[Value])

But I don't understand your / portion of your formula where those numbers are coming from. 

[Anonymous]

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]

@Anonymous My bad, should have been:

Measure = 
  VAR __Date = MAX('Calendar'[Date])
RETURN
  SUMX(FILTER('Table',[Start]>=__Date && __Date<=[End]),[Value])

[Anonymous]

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)