Calculate Stock only on last date of every sub category with correct TOTAL value

[andrehawari]

Hi

I need to create the measure that calculate the sum of last date in the selected date range for every unique subcategory

The example of my sample data

Below is my intended result

d

Explanation:

If I choose the date range between 1 to 4 january 2018 canada will display 6 because it is summing up the value from row 10 and 14.

US will display 11 because it is summing the value from row  4 and 8.

Similarly, If I choose the date range between 1 to 3 January 2018, Canada will display 5 because it is summing  up the value from row 10 and row 13.

i have created this measure:

Stock Measure = CALCULATE(sum(Sheet1[Stock]),generate(values(Sheet1[SubCategory]),LASTDATE(Sheet1[Date]))) 

however, for very large amount of distinct value subcategory (up to 1000 subcategroy), the measure performance is very slow and for some reason, lots of subcategory is excluded from the calculation. Also the total is showing up the wrong value as well

Any idea for the alternative approach ?

P.s. I can't use separate datedimension table, so the date filter  is taken directly from transaction table

I have included the sample  pbix as well

https://drive.google.com/file/d/1ogFH7U_VS0xcQavLIYdqTRjNqKNa1Fy8/view?usp=sharing

thank you

[Ashish_Mathur]

Hi,

Try this measure

=SUMX(SUMMARIZE(VALUES(Sheet1[SubCategory]),[SubCategory],"ABCD",CALCULATE(SUM(Sheet1[Stock]),LASTNONBLANK(Sheet1[Date],SUM(Sheet1[Stock])))),[ABCD])

Hope this helps.

[andrehawari]

Hi @Ashish_Mathur

Thanks for the solution.

I have further question regarding your measure.

Where is the "ABCD" and [ABCD] come from ? is it the list of distinct value of subcategory ?

In the real production data, I will have more than 40000 distinct value of subcategory, therefore it might be difficult to hardcode all of it one by one

Thank you !

andre

[Ashish_Mathur]

Hi,

"ABCD" is just the title of the virtual column created via the SUMMARIZE() function.  The number of distinct subcategories should not involve any additional work.  Try to run this formula on your live data and let me know how it works.

[andrehawari]

Hi @Ashish_Mathur

Noted, Thanks for the ABCD explanation. I implemented your measure and it displayed same result like your capture. 

However, one more problem is the total value is not correct (It supposed to show 15 instead 11 (i created one more simple KPI card to test it)

Thank you

Andre

[Ashish_Mathur · Accepted solution]

Hi,

Try this measure

=IF(HASONEVALUE(Sheet1[Location]),SUMX(SUMMARIZE(VALUES(Sheet1[SubCategory]),[SubCategory],"ABCD",CALCULATE(SUM(Sheet1[Stock]),LASTNONBLANK(Sheet1[Date],SUM(Sheet1[Stock])))),[ABCD]),SUMX(SUMMARIZE(VALUES(Sheet1[Location]),[Location],"EFGH",SUMX(SUMMARIZE(VALUES(Sheet1[SubCategory]),[SubCategory],"ABCD",CALCULATE(SUM(Sheet1[Stock]),LASTNONBLANK(Sheet1[Date],SUM(Sheet1[Stock])))),[ABCD])),[EFGH]))

[andrehawari]

Hi @Ashish_Mathur

It works like a charm ! even my production data as well with millions of row

Thank you very much for your help ! 

Best regards

andre

[Ashish_Mathur]

You are welcome.