date evaluation

Question

Hi everyone I have a problem with a date evaluation.&nbsp;Starting position:List 1: CalendarDateWorking day01.01.2020True02.01.2020True03.01.2020True04.01.2020True05.01.2020False06.01.2020False&nbsp;List 2: Construction programNameStartEndValueA01.01.202003.01.202050B03.01.202005.01.202060C02.01.202006.01.202030D04.01.202006.01.202010&nbsp;Goal:DateValuecalculation only example01.01.202016.66(A/3)02.01.202026.66(A/3 + C/3)03.01.202056.66(A/3 + C/3 + B/2)04.01.202050(B/2 + C/3 + D/1)05.01.20200&nbsp;06.01.20200&nbsp;What is the best way to do this?&nbsp;I've tried several ways, but don't understand how best to get the results. The first attempt was with the formula:&nbsp;&nbsp;&nbsp;List.Dates([VorgangGeplanterAnfangsTermin],Duration.Days(
[VorgangGeplanterEndTermin]-[VorgangGeplanterAnfangsTermin])+1,#duration(1,0,0,0))&nbsp;&nbsp;&nbsp;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?&nbsp;Or do I have to work better with a measure:DATESBETWEEN(&lt;dates&gt;,&lt;start_date&gt;,&lt;end_date&gt;)&nbsp;Maybe can&nbsp;ibarrau&nbsp; help me?you helped me a lot last time, unfortunately my dashboard has become more complex and this solution is no longer sufficient&nbsp;Thanks a lotT

greg_deckler · Answer

Anonymous&nbsp;Seems like:
Measure = 
  VAR __Date = MAX('Calendar'[Date])
RETURN
  SUMX(FILTER('Table',[Start]&gt;=__Date &amp;&amp; [End]&lt;=__Date),[Value])
But I don't understand your / portion of your formula where those numbers are coming from.&nbsp;

anonymous · Answer

hi&nbsp;Greg_Deckler&nbsp;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 daysThat is the reason that on 01.01.2020 only a third of Ai have applied your measure but its empty, what did i do wrong?&nbsp;

amitchandak · Answer

Anonymous , Based on what I got. I have&nbsp; similar problems 
see if this can help
https://www.dropbox.com/s/o2v5ekfiw5057do/Working%20date%20between%20Dates.pbix?dl=0
&nbsp;
or
&nbsp;
https://www.dropbox.com/s/bqbei7b8qbq5xez/leavebetweendates.pbix?dl=0

greg_deckler · Answer

Anonymous&nbsp;My bad, should have been:
Measure = 
  VAR __Date = MAX('Calendar'[Date])
RETURN
  SUMX(FILTER('Table',[Start]&gt;=__Date &amp;&amp; __Date&lt;=[End]),[Value])

anonymous · Answer

hi&nbsp;Greg_Deckler&nbsp;thanks again for the answerunfortunately the result is not correctthe 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 daysso the results have to be:Date&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; Value&nbsp; &nbsp;explanation of the calculation01.01.2020&nbsp; 16.66&nbsp; &nbsp;(The Value of A&nbsp;divided by 3 because its 3 Working Days): 50/3 = 16.6602.01.2020&nbsp; 26.66&nbsp; &nbsp;(A divided by 3 and C divided by 3):&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;50/3 + 30/3 = 26.6603.01.2020&nbsp; 56.66&nbsp; &nbsp;(A divided by 3&nbsp;and B divided by 2 and C divided by 3):&nbsp; &nbsp; &nbsp; 50/3 + 60/2 +&nbsp;30/3 = 56.6604.01.2020&nbsp; 50&nbsp; &nbsp; &nbsp; &nbsp; (B divided by 2 and C divided by 3 and D divided by 1):&nbsp; &nbsp; &nbsp; 60/2 + 30/3 + 10/1 = 5005.01.2020&nbsp; 0&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; (No Working Day)06.01.2020&nbsp; 0&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; (No Working Day)

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

Forum Discussion

date evaluation

5 Replies

Related content

Lazy evaluation

Dataflow evaluation error

Powerbi Desktop stuck 'evaluating'

Expression Evaluate , error

Query Evaluation failed

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

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