Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
andrehawari
Helper II
Helper II

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

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

ca.JPG

 

 

Below is my intended result

2.JPGd

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

 

1 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]))


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

View solution in original post

7 REPLIES 7
Ashish_Mathur
Super User
Super User

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.

 

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

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

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.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

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)

 

 

4.JPG

 

Thank you

Andre

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]))


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

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 ! Smiley Very Happy

 

Best regards

andre

You are welcome.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Helpful resources

Announcements
Sept PBI Carousel

Power BI Monthly Update - September 2024

Check out the September 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

Sept NL Carousel

Fabric Community Update - September 2024

Find out what's new and trending in the Fabric Community.