topic Re: How to get the latest amount filter by year and month in DAX Commands and Tips https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/How-to-get-the-latest-amount-filter-by-year-and-month/m-p/3301982#M123210 <P><a href="javascript:void(0)" data-lia-user-mentions="" data-lia-user-uid="376088" data-lia-user-login="NickzNickz" class="lia-mention lia-mention-user">NickzNickz</a>,</P><P>&nbsp;</P><P>More like this one:</P><P>&nbsp;</P><LI-CODE lang="markup">Mesure = CALCULATE( SUM( 'Table'[Migration] ), 'Table'[Date] = MAX( 'Table'[Date] ) )</LI-CODE><P>&nbsp;</P><P><img /></P><P>&nbsp;</P><P>Best,</P><P>&nbsp;</P> Mon, 26 Jun 2023 08:56:26 GMT Alf94 2023-06-26T08:56:26Z How to get the latest amount filter by year and month https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/How-to-get-the-latest-amount-filter-by-year-and-month/m-p/3301730#M123188 <P>Hi...</P><P>&nbsp;</P><P>I have created the table below for our scenario purposes.</P><P><img /></P><P>and below is the measure I currently have to get the output but only get the latest last record ...<BR />Filter by Year and Month</P><P>&nbsp;</P><LI-CODE lang="markup">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 &amp;&amp; test_scenario_2[Month] = _Month) , test_scenario_2[Migration]) Return IF(ISBLANK(_Result),"0.00",_Result)</LI-CODE><P>&nbsp;</P><P>Anyone can help me to get the below result :</P><P>1)&nbsp; Migration total sum for the latest year and month. The result should get = 1950</P><P>2) Please advise me if the measure can be a more simplified version or the table need an additional column...</P><P>&nbsp;</P><P>&nbsp;</P><P>Regards,</P><P>NickzNickz</P><P>&nbsp;</P> Mon, 26 Jun 2023 06:54:22 GMT https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/How-to-get-the-latest-amount-filter-by-year-and-month/m-p/3301730#M123188 NickzNickz 2023-06-26T06:54:22Z Re: How to get the latest amount filter by year and month https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/How-to-get-the-latest-amount-filter-by-year-and-month/m-p/3301795#M123193 <P>Hi <a href="javascript:void(0)" data-lia-user-mentions="" data-lia-user-uid="376088" data-lia-user-login="NickzNickz" class="lia-mention lia-mention-user">NickzNickz</a> ,</P><P>&nbsp;</P><P>Can you try the following?</P><P>&nbsp;</P><LI-CODE lang="markup">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 &amp;&amp; test_scenario_2[Month] = _month ), test_scenario_2[Migration] )</LI-CODE><P>&nbsp;</P><P>If I answered your question, please mark my post as a solution.</P><P>&nbsp;</P><P>Best,</P> Mon, 26 Jun 2023 07:29:31 GMT https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/How-to-get-the-latest-amount-filter-by-year-and-month/m-p/3301795#M123193 Alf94 2023-06-26T07:29:31Z Re: How to get the latest amount filter by year and month https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/How-to-get-the-latest-amount-filter-by-year-and-month/m-p/3301818#M123197 <P>Hi&nbsp;<a href="javascript:void(0)" data-lia-user-mentions="" data-lia-user-uid="460119" data-lia-user-login="Alf94" class="lia-mention lia-mention-user">Alf94</a>&nbsp;,</P><P>&nbsp;</P><P>I have tested ...&nbsp;<BR /><BR /></P><P>Scenario 1 - ok ..&nbsp;<BR />Scenario 2&nbsp; - I add year 2023 and month 3 , result is empty as below ...</P><P><img /></P><P>Should I create date table and filter max from there ? ...</P><P>&nbsp;</P><P>NickzNickz</P><P>&nbsp;</P> Mon, 26 Jun 2023 07:47:47 GMT https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/How-to-get-the-latest-amount-filter-by-year-and-month/m-p/3301818#M123197 NickzNickz 2023-06-26T07:47:47Z Re: How to get the latest amount filter by year and month https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/How-to-get-the-latest-amount-filter-by-year-and-month/m-p/3301834#M123198 <P><a href="javascript:void(0)" data-lia-user-mentions="" data-lia-user-uid="376088" data-lia-user-login="NickzNickz" class="lia-mention lia-mention-user">NickzNickz</a>,</P><P>&nbsp;</P><P>A date table would certainly be a good idea <span class="lia-unicode-emoji" title=":slightly_smiling_face:">🙂</span> Once created, you could simply sum the migration column, filtered on MAX( dateTable[Date] ).</P> Mon, 26 Jun 2023 07:52:27 GMT https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/How-to-get-the-latest-amount-filter-by-year-and-month/m-p/3301834#M123198 Alf94 2023-06-26T07:52:27Z Re: How to get the latest amount filter by year and month https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/How-to-get-the-latest-amount-filter-by-year-and-month/m-p/3301891#M123203 <P><a href="javascript:void(0)" data-lia-user-mentions="" data-lia-user-uid="460119" data-lia-user-login="Alf94" class="lia-mention lia-mention-user">Alf94</a>&nbsp;,</P><P>&nbsp;</P><P>I have created date table as per your recommendation and below is the result.</P><P><img /></P><P>&nbsp;</P><P>&nbsp;</P><LI-CODE lang="markup">Latest Value Migration 3 = SUMX( FILTER( test_scenario_2,test_scenario_2[Date] = MAX(test_scenario_2[Date]) ), test_scenario_2[Migration])</LI-CODE><P>&nbsp;</P><P><BR />If I want to get only the last value, what should I change in the measure ? ...</P><P>&nbsp;</P><P>NickzNickz</P> Mon, 26 Jun 2023 08:14:15 GMT https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/How-to-get-the-latest-amount-filter-by-year-and-month/m-p/3301891#M123203 NickzNickz 2023-06-26T08:14:15Z Re: How to get the latest amount filter by year and month https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/How-to-get-the-latest-amount-filter-by-year-and-month/m-p/3301982#M123210 <P><a href="javascript:void(0)" data-lia-user-mentions="" data-lia-user-uid="376088" data-lia-user-login="NickzNickz" class="lia-mention lia-mention-user">NickzNickz</a>,</P><P>&nbsp;</P><P>More like this one:</P><P>&nbsp;</P><LI-CODE lang="markup">Mesure = CALCULATE( SUM( 'Table'[Migration] ), 'Table'[Date] = MAX( 'Table'[Date] ) )</LI-CODE><P>&nbsp;</P><P><img /></P><P>&nbsp;</P><P>Best,</P><P>&nbsp;</P> Mon, 26 Jun 2023 08:56:26 GMT https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/How-to-get-the-latest-amount-filter-by-year-and-month/m-p/3301982#M123210 Alf94 2023-06-26T08:56:26Z