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

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

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
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

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

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.