topic Re: Calculate dynamic percentage change quarter on quarter and year on year in DAX Commands and Tips https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/Calculate-dynamic-percentage-change-quarter-on-quarter-and-year/m-p/1061459#M14781 <P>Hi&nbsp;Anonymous</LI-USER>&nbsp;,</P><P>&nbsp;</P><P>Check this file:&nbsp;&nbsp;<A title="PBIX" href="https://drive.google.com/file/d/1oJpn7PlQRQlF3c1anNp0Or104_HRR-n3/view?usp=sharing" target="_blank" rel="noopener">Download PBIX</A>&nbsp;</P><P>The key point is to have a date calendar, so you can use the data intel functions.</P><P>&nbsp;</P><P>Did I answer your question? Mark my post as a solution!<BR />Ricardo</P> Fri, 01 May 2020 17:59:56 GMT camargos88 2020-05-01T17:59:56Z Calculate dynamic percentage change quarter on quarter and year on year https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/Calculate-dynamic-percentage-change-quarter-on-quarter-and-year/m-p/1061369#M14775 <P>Hello,</P><P>&nbsp;</P><P>I am new to Power Bi. I am trying to calculate the percentage change in the number of customers quarter on quarter as well as year on year&nbsp; based filter applied on two columns- Energy and Supplier. I tried below code but I guess I am going wrong with the Date logic.&nbsp;</P><P>&nbsp;</P><P>change perc = VAR ValueLastYear =<BR />CALCULATE (<BR />SUM ( ElectricityGasCustomerNum[CustomerNumber] ),<BR />FILTER (<BR />ElectricityGasCustomerNum,<BR />ElectricityGasCustomerNum[Date]<BR />= ( EARLIER ( ElectricityGasCustomerNum[Date] ) - 1 )<BR />&amp;&amp; ElectricityGasCustomerNum[Energy] = EARLIER ( ElectricityGasCustomerNum[Energy] )<BR />&amp;&amp; ElectricityGasCustomerNum[Supplier] = EARLIER ( ElectricityGasCustomerNum[Supplier] )<BR />)<BR />)<BR />RETURN<BR />IF (<BR />ISBLANK ( ValueLastYear ),<BR />0,<BR />( ElectricityGasCustomerNum[CustomerNumber] - ValueLastYear )<BR />/ ValueLastYear<BR />)</P><P>&nbsp;</P><P>&nbsp;</P><TABLE><TBODY><TR><TD>Date</TD><TD>Energy</TD><TD>Supplier</TD><TD>CustomerNumber</TD><TD>%Change</TD></TR><TR><TD>01/01/2009</TD><TD>Electricity</TD><TD>A</TD><TD>40000</TD><TD>&nbsp;</TD></TR><TR><TD>01/04/2009</TD><TD>Electricity</TD><TD>A</TD><TD>190000</TD><TD>&nbsp;</TD></TR><TR><TD>01/07/2009</TD><TD>Electricity</TD><TD>A</TD><TD>260000</TD><TD>&nbsp;</TD></TR><TR><TD>01/10/2009</TD><TD>Electricity</TD><TD>A</TD><TD>320000</TD><TD>&nbsp;</TD></TR><TR><TD>01/01/2010</TD><TD>Electricity</TD><TD>A</TD><TD>370000</TD><TD>&nbsp;</TD></TR><TR><TD>01/04/2010</TD><TD>Electricity</TD><TD>A</TD><TD>410000</TD><TD>&nbsp;</TD></TR><TR><TD>01/07/2010</TD><TD>Electricity</TD><TD>A</TD><TD>400000</TD><TD>&nbsp;</TD></TR><TR><TD>01/10/2010</TD><TD>Electricity</TD><TD>A</TD><TD>450000</TD><TD>&nbsp;</TD></TR><TR><TD>01/01/2009</TD><TD>Electricity</TD><TD>B</TD><TD>25000</TD><TD>&nbsp;</TD></TR><TR><TD>01/04/2009</TD><TD>Electricity</TD><TD>B</TD><TD>140000</TD><TD>&nbsp;</TD></TR><TR><TD>01/07/2009</TD><TD>Electricity</TD><TD>B</TD><TD>100000</TD><TD>&nbsp;</TD></TR><TR><TD>01/10/2009</TD><TD>Electricity</TD><TD>B</TD><TD>200000</TD><TD>&nbsp;</TD></TR><TR><TD>01/01/2010</TD><TD>Electricity</TD><TD>B</TD><TD>310000</TD><TD>&nbsp;</TD></TR><TR><TD>01/04/2010</TD><TD>Electricity</TD><TD>B</TD><TD>280000</TD><TD>&nbsp;</TD></TR><TR><TD>01/07/2010</TD><TD>Electricity</TD><TD>B</TD><TD>350000</TD><TD>&nbsp;</TD></TR><TR><TD>01/10/2010</TD><TD>Electricity</TD><TD>B</TD><TD>390000</TD><TD>&nbsp;</TD></TR><TR><TD>01/01/2009</TD><TD>Gas</TD><TD>A</TD><TD>30000</TD><TD>&nbsp;</TD></TR><TR><TD>01/04/2009</TD><TD>Gas</TD><TD>A</TD><TD>90000</TD><TD>&nbsp;</TD></TR><TR><TD>01/07/2009</TD><TD>Gas</TD><TD>A</TD><TD>60000</TD><TD>&nbsp;</TD></TR><TR><TD>01/10/2009</TD><TD>Gas</TD><TD>A</TD><TD>30000</TD><TD>&nbsp;</TD></TR><TR><TD>01/01/2010</TD><TD>Gas</TD><TD>A</TD><TD>290000</TD><TD>&nbsp;</TD></TR><TR><TD>01/04/2010</TD><TD>Gas</TD><TD>A</TD><TD>320000</TD><TD>&nbsp;</TD></TR><TR><TD>01/07/2010</TD><TD>Gas</TD><TD>A</TD><TD>390000</TD><TD>&nbsp;</TD></TR><TR><TD>01/10/2010</TD><TD>Gas</TD><TD>A</TD><TD>370000</TD><TD>&nbsp;</TD></TR></TBODY></TABLE><P>&nbsp;</P><P><SPAN>Any help would be greatly appreciated.</SPAN></P><P>&nbsp;</P><P><SPAN>Thanks!</SPAN></P> Fri, 01 May 2020 16:47:27 GMT https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/Calculate-dynamic-percentage-change-quarter-on-quarter-and-year/m-p/1061369#M14775 Anonymous 2020-05-01T16:47:27Z Re: Calculate dynamic percentage change quarter on quarter and year on year https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/Calculate-dynamic-percentage-change-quarter-on-quarter-and-year/m-p/1061459#M14781 <P>Hi&nbsp;Anonymous</LI-USER>&nbsp;,</P><P>&nbsp;</P><P>Check this file:&nbsp;&nbsp;<A title="PBIX" href="https://drive.google.com/file/d/1oJpn7PlQRQlF3c1anNp0Or104_HRR-n3/view?usp=sharing" target="_blank" rel="noopener">Download PBIX</A>&nbsp;</P><P>The key point is to have a date calendar, so you can use the data intel functions.</P><P>&nbsp;</P><P>Did I answer your question? Mark my post as a solution!<BR />Ricardo</P> Fri, 01 May 2020 17:59:56 GMT https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/Calculate-dynamic-percentage-change-quarter-on-quarter-and-year/m-p/1061459#M14781 camargos88 2020-05-01T17:59:56Z Re: Calculate dynamic percentage change quarter on quarter and year on year https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/Calculate-dynamic-percentage-change-quarter-on-quarter-and-year/m-p/1062978#M14837 <P>Hi&nbsp;<a href="javascript:void(0)" data-lia-user-mentions="" data-lia-user-uid="215853" data-lia-user-login="camargos88" class="lia-mention lia-mention-user">camargos88</a>&nbsp;,</P><P>&nbsp;</P><P>Thank you for your solution. It worked. I am building a dashboard for this type of data. It would be great if you could suggest what all insights I can show may be as a summary using the charts of power bi.</P> Sun, 03 May 2020 00:24:32 GMT https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/Calculate-dynamic-percentage-change-quarter-on-quarter-and-year/m-p/1062978#M14837 Anonymous 2020-05-03T00:24:32Z