Measure for most recent transaction date figure

[RichOB]

Hi, I'm trying get the Total_Arrears figure to be just the 1 amount from the Total_Arrears column, not the combined figures of each Transaction_Date.

My source data automatically overrides all previous Transaction_Date amounts with what the current Total_Arrears figure is. I cannot get around this in the CRM. What measure can show the property as having the 1 amount, not the combined amount, please? For example, I want to be able to show the data  broken down as:

North = -£1000 ( 3 properties in Glasgow and Edinburgh owing -£250 + -£250 + -£500)

Glasgow = -£500 (2 properties owing -£250)

P1 = -£250

Area	City	Property	Total_Arrears	Transaction_ID	Transaction_Date
North	Glasgow	P1	-£250	T1	01/01/2025
North	Glasgow	P1	-£250	T2	01/02/2025
North	Glasgow	P2	-£250	T3	01/03/2025
North	Glasgow	P2	-£250	T4	01/04/2025
North	Glasgow	P2	-£250	T4	01/05/2025
North	Edinburgh	P3	-£500	T5	01/01/2025
North	Edinburgh	P3	-£500	T6	01/02/2025
North	Edinburgh	P3	-£500	T7	01/03/2025
Mid	Manchester	P4	-£650	T8	01/01/2025
Mid	Manchester	P4	-£650	T9	01/02/2025
Mid	Manchester	P4	-£650	T10	01/03/2025
Mid	Manchester	P4	-£650	T11	01/04/2025
Mid	Manchester	P4	-£650	T12	01/05/2025
Mid	Manchester	P4	-£650	T13	01/06/2025
South	Bristol	P5	-£200	T14	01/01/2025
South	Bristol	P6	-£200	T15	01/02/2025
South	Bath	P7	-£300	T16	01/01/2025
South	Bath	P7	-£300	T17	01/02/2025
South	Bath	P7	-£300	T18	01/03/2025
South	Brighton	P8	-£200	T19	01/01/2025
South	Brighton	P8	-£200	T20	01/02/2025

Thanks

[v-achippa]

Hi @RichOB,

Thank you for reaching out to Microsoft Fabric Community.

Thank you @Elena_Kalina and @rajendraongole1 for the prompt response.

As we haven’t heard back from you, we wanted to kindly follow up to check if the solution provided by the user resolved your issue? or let us know if you need any further assistance.
If any response resolved your issue, please mark it as "Accept as solution" and click "Yes" if you found it helpful.

Thanks and regards,

Anjan Kumar Chippa

[v-achippa]

Hi @RichOB,

We wanted to kindly follow up to check if the solution provided by the user resolved your issue.

If any response resolved your issue, please mark it as "Accept as solution" and click "Yes" if you found it helpful.

Thanks and regards,

Anjan Kumar Chippa

[rajendraongole1]

Hi @RichOB  -Create a DAX measure that only takes the latest Total_Arrears value per Property

Please find the attached pbix file FYR.

Hope this helps.

[Elena_Kalina · Accepted solution]

Hi @RichOB 

Please try to create a measure

Latest_Arrears = 
VAR LatestDatesPerProperty =
    SUMMARIZE(
        'Table',
        'Table'[Property],
        "MaxDate", MAX('Table'[Transaction_Date])
    )
RETURN
    SUMX(
        LatestDatesPerProperty,
        VAR CurrentProperty = 'Table'[Property]
        VAR CurrentMaxDate = [MaxDate]
        RETURN
            CALCULATE(
                SUM('Table'[Total_Arrears]),
                FILTER(
                    'Table',
                    'Table'[Property] = CurrentProperty &&
                    'Table'[Transaction_Date] = CurrentMaxDate
                )
            )
    )