Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now
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! 🙂
Solved! Go to Solution.
Expertise = List.Accumulate( {Days as from Today}, {Skills and Knowledge}, (Current, Everyday) => Current & Day.LeanAndPractise(Everyday) ) |
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.
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.
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
@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.
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)
@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.
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?
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
113 | |
90 | |
82 | |
76 | |
64 |
User | Count |
---|---|
144 | |
109 | |
108 | |
100 | |
96 |