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

Anonymous
Not applicable

## Calculate SUM of VALUES for LAST RECORDED DATE entry for each Category

Hello,

I want to calculate the SUM of the latest recorded KPI Date for each 'Asset Type' by 'Organisation.'

I have a date filter on my report and would need the latest available value before the filtered date to be displayed.

Example of my data is below:

 KPIDate Organisation AssetType Volume 24.06.23 Maps Laptops 10 24.07.23 Maps Laptops 20 25.07.23 TPO Laptops 5 07.08.23 ONR Laptops 3 01.09.23 DNT Desktop 1 01.10.23 DNT Desktop 2 20.10.23 DNT Laptops 10 01.11.23 DNT Laptops 6

The calculation should give me 36 as it would only take into account the below values

 KPIDate Organisation AssetType Volume 24.07.23 Maps Laptops 20 25.07.23 TPO Laptops 5 07.08.23 ONR Laptops 3 01.10.23 DNT Desktop 2 20.10.23 DNT Laptops 6

If my report was filtered to September

Any help on this would be super appreciated and thank you in advance for any help! 🙂

1 ACCEPTED SOLUTION
Super User

 Expertise = List.Accumulate(        {Days as from Today},        {Skills and Knowledge},        (Current, Everyday) => Current & Day.LeanAndPractise(Everyday))

8 REPLIES 8
Community Support

Hi @Anonymous ,

Based on your description, I created simple samples and you can check the results below:

Since I don't know your other filters, you can make changes to ALL based on the data model, if you need to show only the rows with the most recent data, you need to split the expression and use the true value as a visual filter and pick the value 1.

An attachment for your reference. Hope it helps!

Best regards,
Community Support Team_ Scott Chang

If this post helps then please consider Accept it as the solution to help the other members find it more quickly.

Super User

Hi,

If you select September, then why should the answer be 36?  Shouldn't the answer be 20+5+3+1 = 29?  These number are for dates <= Sep 30.

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

 Expertise = List.Accumulate(        {Days as from Today},        {Skills and Knowledge},        (Current, Everyday) => Current & Day.LeanAndPractise(Everyday))

Anonymous
Not applicable

Hello,

The measure works if nothing is filtered, it correctly provies me the SUM of all of the values with the latest recorded. It doesn't however work with the date filter. For example in the below I have filtered the date for KPI dates from Jan-Mar and the result should be 213 (87 + 126). It is however only showing blanks like the below

Super User

@Anonymous sorry it is not clear what you mean. Can you post another sample data with the example and the expected output? Anyhow if your original question is solved, maybe add another post with the following question. Just a suggestion.

Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤

Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

Anonymous
Not applicable

Hello,

an example of what I mean is like the below. Your measure correctly provides me with the last available value for the selected date period, however it does not 'Total' the categories together. The picture the total should display 167 (180 + 87) but the total is just showing me the latest value available (180)

Super User

@Anonymous try this measure:

``````Measure =

CALCULATE (
SUM ( 'Table'[Volume] ),
KEEPFILTERS (
TOPN (
1,
FILTER (
ALLSELECTED ( 'Table' ),
'Table'[AssetType] = MAX ( 'Table'[AssetType] ) &&
'Table'[Organisation] = MAX ( 'Table'[Organisation] )
),
CALCULATE ( MAX ( 'Table'[KPIDate] ) ), DESC
)
)
)``````

Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤

Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

Anonymous
Not applicable

Thank you for helping on this, it works for displaying the last recorded value for each asset type by organisation. It doesn't however SUM all of those results together though, any help on this?

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

#### Power BI Monthly Update - July 2024

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

#### Fabric Community Update - July 2024

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

Top Solution Authors
Top Kudoed Authors