Cumulative Inventory Sum Measure

[Anonymous]

Hi all, I have looked high and low on this forum, and can't find a solution for this.  I'm looking to create a cumulative sum by quantity of inventory quantities (the 'Quantity' field shown below) where there are Current Inventory, Sales Orders, Production Orders, Purchase Orders listed by item, date and site.  Inventory values are always shown with the current date, which I built in Power Query, but am willing to change if there is a better solution.  Here is an example - how do I create a measure that captures this?  Thanks in advance!

ID	Item	Date	Site	Type	Quantity
Inventory	1003162	9/10/2020	GARD1	Inventory	12
3010209664	1003162	9/11/2020	GARD1	Sales Order	-3
3010208690	1003162	9/4/2020	IVYL1	Sales Order	-3
3010208414	1003162	9/4/2020	IVYL1	Sales Order	-10
3010208865	1003162	9/8/2020	IVYL1	Sales Order	-4
3010208865	1003162	9/8/2020	IVYL1	Sales Order	-1
3010208818	1003162	9/8/2020	IVYL1	Sales Order	-1
3010208987	1003162	9/9/2020	IVYL1	Sales Order	-2
3010207540	1003162	9/9/2020	IVYL1	Sales Order	-5
Inventory	1003162	9/10/2020	IVYL1	Inventory	994
3010209215	1003162	9/10/2020	IVYL1	Sales Order	-1
3010208386	1003162	9/10/2020	IVYL1	Sales Order	-14
3010209393	1003162	9/10/2020	IVYL1	Sales Order	-1
3010209716	1003162	9/11/2020	IVYL1	Sales Order	-1
3010209946	1003162	9/14/2020	IVYL1	Sales Order	-2
3010209944	1003162	9/14/2020	IVYL1	Sales Order	-4
3010199453	1003162	9/18/2020	IVYL1	Sales Order	-133
3010206877	1003162	9/28/2020	IVYL1	Sales Order	-1
3010206879	1003162	9/28/2020	IVYL1	Sales Order	-1
3010205767	1003162	9/28/2020	IVYL1	Sales Order	-1
3010205763	1003162	9/28/2020	IVYL1	Sales Order	-1
Inventory	1003162	9/10/2020	WARM1	Inventory	0
100016984	1003162	9/28/2020	WARM1	Production Orders	1064

[amitchandak]

@Anonymous , Create a date table and join it with your table and try a measure like

Cumm Sales = CALCULATE(SUM(Table[Quantity]),filter(date,date[date] <=maxx(date,date[date])))

To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :
https://radacad.com/creating-calendar-table-in-power-bi-using-dax-functions
https://www.archerpoint.com/blog/Posts/creating-date-table-power-bi
https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/

See if my webinar on Time Intelligence can help: https://community.powerbi.com/t5/Webinars-and-Video-Gallery/PowerBI-Time-Intelligence-Calendar-WTD-YTD-LYTD-Week-Over-Week/m-p/1051626#M184


Appreciate your Kudos.

[Anonymous]

Hi @amitchandak , thanks for the response, however this measure not work.  I have the date table (TableFiscalCalendar) marked as a date table and there is a relationship between Table and TableFiscalCalendar using Table[Date] and TableFiscalCalendar[GregorianDate] fields.  

Cumm Sum = CALCULATE(SUM(Stock[Quantity]),filter(TableFiscalCalendar,TableFiscalCalendar[GregorianDate] <=maxx(TableFiscalCalendar,TableFiscalCalendar[GregorianDate])))

Should this be adjusted?

[v-lili6-msft · Accepted solution]

hi  @Anonymous 

You are missing ALL in your formula, just adjust it as below:

Cumm Sum =
CALCULATE (
    SUM ( Stock[Quantity] ),
    FILTER (
        ALL ( TableFiscalCalendar ),
        TableFiscalCalendar[GregorianDate] <= MAX ( TableFiscalCalendar[GregorianDate] )
    )
)

Regards,

Lin

[Greg_Deckler]

@Anonymous - Can you just use the Running Total Quick Measure built-in to Power BI Desktop?

[Anonymous]

@Greg_Deckler, the running total quick measure has not worked - I've used this criteria and the field only brings in the same value for Quantity: