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

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Helper I

## 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 @Raymz112 ,

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
https://www.linkedin.com/in/excelenthusiasts/
Super User

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

Helper I

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

@Raymz112 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.

Helper I

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

@Raymz112 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.

Helper I

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?

## Helpful resources

Announcements

#### Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

#### Power BI Monthly Update - April 2024

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

#### Fabric Community Update - April 2024

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

Top Solution Authors
Top Kudoed Authors