topic Get latest value and latest sum value based on year and month in DAX Commands and Tips

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

NickzNickz — Fri, 23 Jun 2023 08:22:25 GMT

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

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

devanshi — Fri, 23 Jun 2023 08:26:26 GMT

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

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

NickzNickz — Sat, 24 Jun 2023 15:28:57 GMT

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

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

NickzNickz — Mon, 26 Jun 2023 01:53:20 GMT

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

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

NickzNickz — Tue, 27 Jun 2023 02:15:28 GMT

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