How to get the latest amount filter by year and month

[NickzNickz]

Hi...

I have created the table below for our scenario purposes.

and below is the measure I currently have to get the output but only get the latest last record ...
Filter by Year and Month

Latest Value Migration = 
VAR _Year = MAX(test_scenario_2[Year])
VAR _Month = MAX(test_scenario_2[Month])
VAR _Maxdate = MAXX(FILTER(test_scenario_2, test_scenario_2[Year] = _Year), test_scenario_2[Month] = _Month)
VAR _Result = MAXX(FILTER(test_scenario_2, test_scenario_2[Year] = _Year && test_scenario_2[Month] = _Month) , test_scenario_2[Migration])
Return
IF(ISBLANK(_Result),"0.00",_Result)

Anyone can help me to get the below result :

1)  Migration total sum for the latest year and month. The result should get = 1950

2) Please advise me if the measure can be a more simplified version or the table need an additional column...

Regards,

NickzNickz

[Alf94 · Accepted solution]

@NickzNickz,

A date table would certainly be a good idea 🙂 Once created, you could simply sum the migration column, filtered on MAX( dateTable[Date] ).

[Alf94]

Hi @NickzNickz ,

Can you try the following?

Latest value migration =
VAR _year = MAX(test_scenario_2[Year])
VAR _month = MAX(test_scenario_2[Month])
RETURN
    SUMX(
        FILTER(
            test_scenario_2,
            test_scenario_2[Year] = _year && test_scenario_2[Month] = _month
        ),
        test_scenario_2[Migration]
    )

If I answered your question, please mark my post as a solution.

Best,

[NickzNickz]

Hi @Alf94 ,

I have tested ... 

Scenario 1 - ok .. 
Scenario 2  - I add year 2023 and month 3 , result is empty as below ...

Should I create date table and filter max from there ? ...

NickzNickz

[Alf94 · Accepted solution]

@NickzNickz,

A date table would certainly be a good idea 🙂 Once created, you could simply sum the migration column, filtered on MAX( dateTable[Date] ).

[NickzNickz]

@Alf94 ,

I have created date table as per your recommendation and below is the result.

Latest Value Migration 3 = 
    SUMX(
        FILTER(
            test_scenario_2,test_scenario_2[Date] 
            = MAX(test_scenario_2[Date])
        ),
        test_scenario_2[Migration])

If I want to get only the last value, what should I change in the measure ? ...

NickzNickz

[Alf94 · Accepted solution]

@NickzNickz,

More like this one:

Mesure = 
    CALCULATE(
        SUM( 'Table'[Migration] ),
        'Table'[Date] = MAX( 'Table'[Date] )
    )

Best,