Forum Discussion
SUM 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.
Hi 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?