Get latest value and latest sum value based on year and month

[NickzNickz]

Hi ... 

I attached herewith the sample I have used for the testing environment :

Scenario #1

Year	Month	New	Migration
2021	3	123	123
2021	6	234	345
2021	9	456	567
2021	12	678	789
2022	3	234	321
2022	6	345	432
2022	9	456	543
2022	12	567	654

Scenario #2

State	Year	Month	New	Migration
A	2021	3	123	123
B	2021	3	234	345
C	2021	3	456	567
D	2021	3	678	789
A	2022	6	234	321
B	2022	6	345	432
C	2022	6	456	543
D	2022	6	567	654

How can I achieve the below results:

Scenario #1:

How can I get the latest value based on year and month for New and Migration?

Scenario #2:

How can I get the latest value (sum) based on year and month for New and Migration?

Thank you and advance.

Regards,

NickzNickz

[devanshi]

Scenario1 :
MaxDate = MAX('Table'[Date])

LatestNewValue = CALCULATE( MAX('Table'[Value]), ALLEXCEPT('Table'[New], 'Table'[Date] = [MaxDate]) ) LatestMigrationValue = CALCULATE( MAX('Table'[Value]), ALLEXCEPT('Table'[Migration], 'Table'[Date] = [MaxDate]) )

Scenario2:

MaxDate = MAX('YourTable'[Date])

LatestNewSum = CALCULATE( SUM('Table'[Value]), ALLEXCEPT('Table'[New], 'Table'[Date] = [MaxDate]) ) LatestMigrationSum = CALCULATE( SUM('Table'[Value]), ALLEXCEPT('Table'[Migration], 'Table'[Date] = [MaxDate] ))

[NickzNickz]

Hi @devanshi ,

I have changed the data in the table and below is the measure  :

Max Date = MAX(test_scenario_2[Date])

Latest Value Test = CALCULATE(MAX(test_scenario_2[Migration]),ALLEXCEPT(test_scenario_2, test_scenario_2[Date] = [Max Date]))

 I tried to match the measure with yours but I couldn't get .... 

[NickzNickz · Accepted solution]

@devanshi ,

I have made some changes to the measure based on conversion with others:

Latest Group Value Migration Final = 
// Get total amount
VAR _Result =
    SUMX(
        FILTER(
            test_scenario_2,test_scenario_2[Date] 
            = MAX(test_scenario_2[Date])
        ),
        test_scenario_2[Migration]
    )
RETURN
IF(ISBLANK(_Result),"0.00",_Result)

Latest Value Migration Final = 
// Get the last amount
VAR _Result = CALCULATE(
        SUM( test_scenario_3[Migration] ),
        test_scenario_3[Date] = MAX( test_scenario_3[Date] )
    )
RETURN
IF(ISBLANK(_Result),"0.00",_Result)

Regards,

NickzNickz

[NickzNickz]

Hi @devanshi ,

1)  'Table'[Value] is a table name ? ...

2) If I have only year and month column, what should I do with MAX('Table'[Date]) ?

Regards,

NickzNickz