Forum Discussion
Microsoft EmployeeSUM between two dates referencing Date table
HelloTom , In this case, do not join the date table with any date or keep the join inactive
try measure like
BatteriesAvailable =
var _max = MAXX(Allselected(DIM_Date), DIM_Date[Date])
return
SUMX(
FILTER(
FACT_Battery,
FACT_Battery[DateKey (Date_Built)] >=_max
&&
FACT_Battery[Date_EndOfLife(5days)] <= _max
)
,FACT_Battery[BatteriesBuilt]
)Also refer this, how to handle inactive joins
Hi HelloTom
Can you explain the calculation logic of the value of the field [BatteriesAvailable] that I have circled ? It can be understood that it is cumulative addition, but the following values should all be 9. Why is there a sequential decrease ?
Best Regards
Community Support Team _ Ailsa Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
HelloTomHi Anonymous -
Good question. It is because each battery only lasts for 5 days (i.e. after 5 days the battery has an 'EndOfLife' and is no longer 'available' to be used (see FACT table picture for context). I've expanded the desired output to show you what I mean (see picture below). Does the logic now make sense?
- HelloTom
The Batteries Built column logic is based on each battery having a lifespan of only 5 days (i.e. after 5 days it has been fully used and thus is no longer available). Given this, the sequential decrease results from the initial built batteries reaching their end of life combined with no additional batteries being produced.