topic Need to get Ending numbers with a Date measure in DAX Commands and Tips https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/Need-to-get-Ending-numbers-with-a-Date-measure/m-p/2813655#M89132 <P>Hi I'm working on Dax function to get data based on the Starting HC &amp; Ending HC&nbsp;</P><P>Starting HC is previous Dec month number &amp; Ending will current month say for eg if choosing 2021 in filter&nbsp; my starting will be Dec'20 &amp; ending will Dec'21 same like if i select 2022 Aug will be my&nbsp; ending HC, i have already found ending HC however i'm not to arrive the opening HC. I almost found DAX the starting HC date but i'm not able use that Starting date to filter the HC.&nbsp;</P><P>this code works perfectly fine for Starting HC</P><DIV><DIV><DIV><SPAN>Selected Ending HC = </SPAN></DIV><DIV><SPAN>VAR</SPAN> <SPAN>A</SPAN><SPAN> = </SPAN><SPAN>MAX</SPAN><SPAN>(</SPAN><SPAN>'Raw Data HC &amp; Attrition'[Report Date]</SPAN><SPAN>)</SPAN></DIV><DIV><SPAN>VAR</SPAN> <SPAN>B</SPAN><SPAN> = </SPAN><SPAN>CALCULATE</SPAN><SPAN>(</SPAN><SPAN>SUM</SPAN><SPAN>(</SPAN><SPAN>'Raw Data HC &amp; Attrition'[HC]</SPAN><SPAN>),</SPAN><SPAN>FILTER</SPAN><SPAN>(</SPAN><SPAN>'Raw Data HC &amp; Attrition'</SPAN><SPAN>,</SPAN><SPAN>'Raw Data HC &amp; Attrition'[Report Date]</SPAN><SPAN>=</SPAN><SPAN>A</SPAN><SPAN>))</SPAN></DIV><DIV><SPAN>RETURN</SPAN></DIV><DIV><SPAN>B</SPAN></DIV><DIV>&nbsp;</DIV><DIV><SPAN>but&nbsp; for Starting</SPAN></DIV><DIV>DAX for finding Starting HC date</DIV><DIV><DIV><DIV><SPAN>Selected End date = </SPAN></DIV><DIV><SPAN>VAR</SPAN> <SPAN>__max</SPAN><SPAN> =</SPAN><SPAN>MAXX</SPAN><SPAN>(</SPAN><SPAN>'Raw Data HC &amp; Attrition'</SPAN><SPAN>,</SPAN><SPAN>'Raw Data HC &amp; Attrition'[Report Date]</SPAN><SPAN>.</SPAN><SPAN>[Date]</SPAN><SPAN>)</SPAN></DIV><DIV><SPAN>VAR</SPAN> <SPAN>__year</SPAN><SPAN> = </SPAN><SPAN>YEAR</SPAN><SPAN>(</SPAN><SPAN>__max</SPAN><SPAN>)</SPAN></DIV><DIV><SPAN>VAR</SPAN> <SPAN>__day</SPAN><SPAN> = </SPAN><SPAN>DAY</SPAN><SPAN>(</SPAN><SPAN>__max</SPAN><SPAN>)</SPAN></DIV><DIV><SPAN>VAR</SPAN> <SPAN>__month</SPAN><SPAN> = </SPAN><SPAN>MONTH</SPAN><SPAN>(</SPAN><SPAN>__max</SPAN><SPAN>)</SPAN></DIV><DIV><SPAN>RETURN</SPAN></DIV><DIV><SPAN>DATE</SPAN><SPAN>(</SPAN><SPAN>__year</SPAN><SPAN>,</SPAN><SPAN>__month</SPAN><SPAN>-</SPAN><SPAN>__month</SPAN><SPAN>,</SPAN><SPAN>__day</SPAN><SPAN>)</SPAN></DIV><DIV>&nbsp;</DIV><DIV><SPAN>I'm getting answer for this, but if i further use this to dax to calculate sum HC, i'm not getting the answer i only get the answer if i selected all dates if choose 2021 in filter its showing blank</SPAN></DIV><DIV>&nbsp;</DIV><DIV><DIV><DIV><SPAN>New Starting End HC = </SPAN></DIV><DIV><SPAN>VAR</SPAN> <SPAN>A</SPAN><SPAN> = </SPAN><SPAN>[Selected End date]</SPAN></DIV><DIV><SPAN>VAR</SPAN> <SPAN>B</SPAN><SPAN> = </SPAN><SPAN>CALCULATE</SPAN><SPAN>(</SPAN><SPAN>SUM</SPAN><SPAN>(</SPAN><SPAN>'Raw Data HC &amp; Attrition'[HC]</SPAN><SPAN>),</SPAN><SPAN>FILTER</SPAN><SPAN>(</SPAN><SPAN>'Raw Data HC &amp; Attrition'</SPAN><SPAN>,</SPAN><SPAN>'Raw Data HC &amp; Attrition'[Report Date]</SPAN><SPAN>=</SPAN><SPAN>A</SPAN><SPAN>))</SPAN></DIV><DIV><SPAN>RETURN</SPAN></DIV><DIV><SPAN>B</SPAN></DIV><DIV>&nbsp;</DIV><DIV><SPAN>please do the needful</SPAN></DIV></DIV></DIV><DIV>&nbsp;</DIV><DIV>&nbsp;</DIV></DIV></DIV></DIV></DIV> Mon, 03 Oct 2022 10:35:09 GMT Anonymous 2022-10-03T10:35:09Z Need to get Ending numbers with a Date measure https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/Need-to-get-Ending-numbers-with-a-Date-measure/m-p/2813655#M89132 <P>Hi I'm working on Dax function to get data based on the Starting HC &amp; Ending HC&nbsp;</P><P>Starting HC is previous Dec month number &amp; Ending will current month say for eg if choosing 2021 in filter&nbsp; my starting will be Dec'20 &amp; ending will Dec'21 same like if i select 2022 Aug will be my&nbsp; ending HC, i have already found ending HC however i'm not to arrive the opening HC. I almost found DAX the starting HC date but i'm not able use that Starting date to filter the HC.&nbsp;</P><P>this code works perfectly fine for Starting HC</P><DIV><DIV><DIV><SPAN>Selected Ending HC = </SPAN></DIV><DIV><SPAN>VAR</SPAN> <SPAN>A</SPAN><SPAN> = </SPAN><SPAN>MAX</SPAN><SPAN>(</SPAN><SPAN>'Raw Data HC &amp; Attrition'[Report Date]</SPAN><SPAN>)</SPAN></DIV><DIV><SPAN>VAR</SPAN> <SPAN>B</SPAN><SPAN> = </SPAN><SPAN>CALCULATE</SPAN><SPAN>(</SPAN><SPAN>SUM</SPAN><SPAN>(</SPAN><SPAN>'Raw Data HC &amp; Attrition'[HC]</SPAN><SPAN>),</SPAN><SPAN>FILTER</SPAN><SPAN>(</SPAN><SPAN>'Raw Data HC &amp; Attrition'</SPAN><SPAN>,</SPAN><SPAN>'Raw Data HC &amp; Attrition'[Report Date]</SPAN><SPAN>=</SPAN><SPAN>A</SPAN><SPAN>))</SPAN></DIV><DIV><SPAN>RETURN</SPAN></DIV><DIV><SPAN>B</SPAN></DIV><DIV>&nbsp;</DIV><DIV><SPAN>but&nbsp; for Starting</SPAN></DIV><DIV>DAX for finding Starting HC date</DIV><DIV><DIV><DIV><SPAN>Selected End date = </SPAN></DIV><DIV><SPAN>VAR</SPAN> <SPAN>__max</SPAN><SPAN> =</SPAN><SPAN>MAXX</SPAN><SPAN>(</SPAN><SPAN>'Raw Data HC &amp; Attrition'</SPAN><SPAN>,</SPAN><SPAN>'Raw Data HC &amp; Attrition'[Report Date]</SPAN><SPAN>.</SPAN><SPAN>[Date]</SPAN><SPAN>)</SPAN></DIV><DIV><SPAN>VAR</SPAN> <SPAN>__year</SPAN><SPAN> = </SPAN><SPAN>YEAR</SPAN><SPAN>(</SPAN><SPAN>__max</SPAN><SPAN>)</SPAN></DIV><DIV><SPAN>VAR</SPAN> <SPAN>__day</SPAN><SPAN> = </SPAN><SPAN>DAY</SPAN><SPAN>(</SPAN><SPAN>__max</SPAN><SPAN>)</SPAN></DIV><DIV><SPAN>VAR</SPAN> <SPAN>__month</SPAN><SPAN> = </SPAN><SPAN>MONTH</SPAN><SPAN>(</SPAN><SPAN>__max</SPAN><SPAN>)</SPAN></DIV><DIV><SPAN>RETURN</SPAN></DIV><DIV><SPAN>DATE</SPAN><SPAN>(</SPAN><SPAN>__year</SPAN><SPAN>,</SPAN><SPAN>__month</SPAN><SPAN>-</SPAN><SPAN>__month</SPAN><SPAN>,</SPAN><SPAN>__day</SPAN><SPAN>)</SPAN></DIV><DIV>&nbsp;</DIV><DIV><SPAN>I'm getting answer for this, but if i further use this to dax to calculate sum HC, i'm not getting the answer i only get the answer if i selected all dates if choose 2021 in filter its showing blank</SPAN></DIV><DIV>&nbsp;</DIV><DIV><DIV><DIV><SPAN>New Starting End HC = </SPAN></DIV><DIV><SPAN>VAR</SPAN> <SPAN>A</SPAN><SPAN> = </SPAN><SPAN>[Selected End date]</SPAN></DIV><DIV><SPAN>VAR</SPAN> <SPAN>B</SPAN><SPAN> = </SPAN><SPAN>CALCULATE</SPAN><SPAN>(</SPAN><SPAN>SUM</SPAN><SPAN>(</SPAN><SPAN>'Raw Data HC &amp; Attrition'[HC]</SPAN><SPAN>),</SPAN><SPAN>FILTER</SPAN><SPAN>(</SPAN><SPAN>'Raw Data HC &amp; Attrition'</SPAN><SPAN>,</SPAN><SPAN>'Raw Data HC &amp; Attrition'[Report Date]</SPAN><SPAN>=</SPAN><SPAN>A</SPAN><SPAN>))</SPAN></DIV><DIV><SPAN>RETURN</SPAN></DIV><DIV><SPAN>B</SPAN></DIV><DIV>&nbsp;</DIV><DIV><SPAN>please do the needful</SPAN></DIV></DIV></DIV><DIV>&nbsp;</DIV><DIV>&nbsp;</DIV></DIV></DIV></DIV></DIV> Mon, 03 Oct 2022 10:35:09 GMT https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/Need-to-get-Ending-numbers-with-a-Date-measure/m-p/2813655#M89132 Anonymous 2022-10-03T10:35:09Z Re: Need to get Ending numbers with a Date measure https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/Need-to-get-Ending-numbers-with-a-Date-measure/m-p/2816497#M89329 <P><FONT>Hi,&nbsp;Anonymous</LI-USER></FONT>&nbsp;</P> <P>Sorry, I'm a little confused by your statement.&nbsp;Can you provide a sample pbix for research?</P> <P>Is the first valid code you provided referring to "Strating HC" or "SelectedEnding HC"?</P> <P>&nbsp;</P> <P><img /></P> <P>&nbsp;</P> <P><FONT>Best Regards,<BR />Community Support Team _ Eason</FONT></P> Tue, 04 Oct 2022 09:51:36 GMT https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/Need-to-get-Ending-numbers-with-a-Date-measure/m-p/2816497#M89329 v-easonf-msft 2022-10-04T09:51:36Z Re: Need to get Ending numbers with a Date measure https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/Need-to-get-Ending-numbers-with-a-Date-measure/m-p/2816649#M89336 <P>Starting HC is nothing but Ending numbers..<img /></P> Tue, 04 Oct 2022 10:54:46 GMT https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/Need-to-get-Ending-numbers-with-a-Date-measure/m-p/2816649#M89336 Anonymous 2022-10-04T10:54:46Z