Cumulative Sum of Stock in a matrix table

[leandross89]

Hi Dare,

I am trying do a cumulative sum that sum values in a column per year and after that shows the result in a matriz table. 

my table is like that:

Year	Component	Amount	Type
2019	Mosfet/T2	1	Consumed
2020	Mosfet/T3	1	Consumed
2019	OPA692/U23	1	Consumed
2019	Mosfet/T2	100	purchased
2019	Mosfet/T2	10	purchased
2019	OPA692/U23	10	purchased

I have two mesuares to sum the amount of "consumed and purchased" and I want to create one more measures to sum the stock amount but the way cumulative. In my chart shows stock per year (but isn´t cumulative) , but I need to show the stock as it was before and as it is currently.

my code to know the stock amount:

Stock1 = CALCULATE(SUM('table1'[amount]); 'table1'[Type]="purchased") - CALCULATE( SUM('table1'[amount]);'table1'[Type]="Consumed")

my code to cumulate the stock per year below:

Stock = CALCULATE([Stock1];FILTER(ALL(dCalendar);dCalendar[Date]<=MAX(dCalendario[Date])))

I dont know why isn´t works, Could you help me with? 

[v-deddai1-msft]

Hi @leandross89 ,

Would you please try to use the following measures :

Consumed = CALCULATE(SUM('Table'[Amount]), 'Table'[Type] = "Consumed")

purchased = CALCULATE(SUM('Table'[Amount]),'Table'[Type] = "purchased")

stock =

var currentYear = MIN('Table'[Year])

return

CALCULATE([purchased]-[Consumed],ALLSELECTED('Table'[Year]),'Table'[Year]<=currentYear)

We use the year column of fact table as the column of this matrix visual.

If it does not work, could you please describe the relationship between your calendar table and the fact table? What is the column you used as the column field of matrix visual?

For more details, please refer to pbix file :https://qiuyunus-my.sharepoint.com/:u:/g/personal/pbipro_qiuyunus_onmicrosoft_com/ESrQmIjBDbFMszm4Z_...

Best Regards,

Dedmon Dai

[leandross89]

@v-deddai1-msft  Hello,

I try your dax, but it is lacking some results in our chart. example in 2019 nothing was consumed or purchased I saw that nothing appear with that dax, I want that show the cumulative stock before that, like values of 2018,2017 in the stock of 2019 ( if exist something before).

[leandross89]

@Ashish_Mathur @v-deddai1-msft 

The result in my chart was that: (the both dax) Stock not appear when there isn´t value in some period

[amitchandak]

@leandross89 

For Year Cumulative you should use YTD. With Date calendar. FOr overall you can use cumulative

YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(('Date'[Date]),"12/31"))
This Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD((ENDOFYEAR('Date'[Date])),"12/31"))
Last YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(dateadd('Date'[Date],-1,Year),"12/31"))
Last YTD complete Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(ENDOFYEAR(dateadd('Date'[Date],-1,Year)),"12/31"))
Last to last YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(dateadd('Date'[Date],-2,Year),"12/31"))
Year behind Sales = CALCULATE(SUM(Sales[Sales Amount]),dateadd('Date'[Date],-1,Year))


Cumm Sales = CALCULATE(SUM(Sales[Sales Amount]),filter(date,date[date] <=maxx(date,date[date])))
Cumm Sales = CALCULATE(SUM(Sales[Sales Amount]),filter(date,date[date] <=max(Sales[Sales Date])))
Cumm Days = CALCULATE(distinctcount(date[date]),filter(date,date[date] <=max(Sales[Sales 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/

[Ashish_Mathur · Accepted solution]

Hi,

You may download my PBI file from here.

Hope this helps.

[leandross89]

Finally I got to finish that, I received that You create 2 collumns , 1 for purchased and 1 for consumed. So I organized my table the same way you made, after that it shows all cumulative stock in all collumns . 

(Before I had 1 collumn with type (consumed and purchased) and 1 column with amount of each item. I don´t know why my measuares = each item not worked. 

Thank you a lot for help

[Ashish_Mathur]

You are welcome.

[leandross89]

Hello @Ashish_Mathur 

In your pbix I saw that there is a collumn called like date (dd/mm/yyyy) in the table data, but in my pbix there is only the collumn year (YYYY) in my table data, our table calendar is equal.
I don´t know how I do to my table date has a collumn with complete data too (dd/mm/yyyy).
When I used your dax it doesn´t work, I think because that I mentioned above.

[CheenuSing]

Hi @leandross89 ,

Can you upload the pbix / data to Goodgle drive / One Drive and share the link here ?

Cheers

CheenuSing