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.

Reply
Raymz112
Helper I
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
ThxAlot
Super User
Super User

ThxAlot_0-1700857200320.png



Expertise = List.Accumulate(


        {Days as from Today},


        {Skills and Knowledge},


        (Current, Everyday) => Current & Day.LeanAndPractise(Everyday)


)



View solution in original post

8 REPLIES 8
v-tianyich-msft
Community Support
Community Support

Hi @Raymz112 ,

 

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

vtianyichmsft_0-1701140661436.png

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.

 

Ashish_Mathur
Super User
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/
ThxAlot
Super User
Super User

ThxAlot_0-1700857200320.png



Expertise = List.Accumulate(


        {Days as from Today},


        {Skills and Knowledge},


        (Current, Everyday) => Current & Day.LeanAndPractise(Everyday)


)



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

Raymz112_1-1701094734205.png

 

parry2k
Super User
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.

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)

Raymz112_0-1701094372860.png

 

parry2k
Super User
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.

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

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

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

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

April Fabric Community Update

Fabric Community Update - April 2024

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