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

[Raymz112]

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! 🙂

[v-tianyich-msft]

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.

[Ashish_Mathur]

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.

[ThxAlot · Accepted solution]

[Raymz112]

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

[parry2k]

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

[Raymz112]

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)

[parry2k]

@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
        )
    )
)

[Raymz112]

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?