Cumulative/Rolling Sum Blank Dates

[Anonymous]

Hi community!

I'm currently working on inventory reconciliation, and I've struggling to fill all days of the calendar with the cumulative sum of product we're currently storing: 

Inventory level ($). = CALCULATE(SUM(ledger[cost]),FILTER(ALL(DimDate[Date]),DimDate[Date]<=MAX(ledger[Document Date])))

As you guys might notice it has at least 90% of all dates filled, however if we look closely to the graph, we can appreaciate March 5th of 2016 is missing just due to the fact there was no transaction during that day resulting on a blank value. However I'm trying to accomplish retrieving the previous day balance for those days with no transactions. e.g: for March 5th should have $17,038,462.32 (balance for the previous day March 4th).

I'm trying to work on another clause into the measure with functions such as EARLIER or LASTDATE, however I haven't been succesful.

Any insight or solutions works well thank you. Have a nice day.

[mwegener · Accepted solution]

Hi @Anonymous,

try this out.

Inventory level ($). = CALCULATE(SUM(ledger[cost]),FILTER(ALL(DimDate[Date]),DimDate[Date]<=MAX(DimDate[Date])))

Make sure that you use DimDate[Date] in your table visual.

Regards,

Marcus

Dortmund - Germany
If I answered your question, please mark my post as solution, this will also help others.
Please give Kudos for support.