CODEWITHSUNDEEP
powerbidaxpowerbi-desktop
J-Saw
J-Saw

Reputation: 49

Cumulative/Rolling Sum Blank Dates

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:

enter image description here

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.

Upvotes: 1

Views: 1012

Answers (1)

RADO
RADO

Reputation: 8148

You are using a wrong date field in your measure. Change it to the field from the Date table:

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

Upvotes: 1

Related Questions