Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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
Solved! Go to Solution.
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
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] ))
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 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
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
User | Count |
---|---|
54 | |
23 | |
19 | |
18 | |
17 |
User | Count |
---|---|
91 | |
86 | |
46 | |
28 | |
22 |