SUM between two dates referencing Date table

[HelloTom]

Hello!

First, a quick thank you to anyone able to help here, especially if this isn't your day job. 

What is in this linked folder?

1. .pbix: sample data, model, and FACT/DIM relationships
2. Excel: raw sample data and desired output example

What is my objective?
I am trying to produce a 'BatteriesAvailable' calculated measure by a sum of all 'BatteriesBuilt' values in my 'FACT_Battery' table where 'FACT_Battery[DateBuilt]' <= 'DIM_Date[Date]' <= 'FACT_Battery[Date_EndOfLife(5days)]'.  Ideally, I would also like to have a % of total column but my main priority is having the calculated 'BatteriesAvailable' measure.  The desired output would be a table with 3+ columns: 'Date' (from DIM_Date), 'BatteriesAvailable' (from FACT_Battery), and '%ofTotal' (from FACT_Battery).

What have I tried so far?
From this forumn and general research I have come up with the below DAX but it is not producing the desired result.  Any help would be appreciated!

BatteriesAvailable =

SUMX(

FILTER(

FACT_Battery,

FACT_Battery[DateKey (Date_Built)] <= RELATED(DIM_Date[Date])

&&

FACT_Battery[Date_EndOfLife(5days)] >= RELATED(DIM_Date[Date])

)

,FACT_Battery[BatteriesBuilt]

)

[Anonymous]

Hi @HelloTom 

The field [BatteriesRecycled] is from FACT_Battery table , so can you provide the original data for the field [BatteriesRecycled] ?

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.

[Anonymous]

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.

[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.

[HelloTom]

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?

[amitchandak · Accepted solution]

@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

https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-tr...

[HelloTom]

The above link you provided to post you have on this type of scenario was helpful, thank you!  The above DAX didn't return the exact desired outcome but I was able to figure it out based on the DAX you posted and your link provided.  Thank you again.