cancel
Showing results 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

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

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

thank you

1 ACCEPTED SOLUTION
Super User

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
7 REPLIES 7
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.

Regards,
Ashish Mathur
http://www.ashishmathur.com
Helper II

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

Super User

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
Helper II

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

Super User

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
Helper II

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

Super User

You are welcome.

Regards,
Ashish Mathur
http://www.ashishmathur.com

Announcements

#### 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.

#### Power BI Monthly Update - February 2024

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

#### Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

#### Microsoft Fabric Partner Community

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

Top Solution Authors
Top Kudoed Authors