topic Re: Quarterly Growth Calculation in DAX Commands and Tips https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/Quarterly-Growth-Calculation/m-p/3142786#M112275 <P>Set up a proper date table, marked as a date table, and you can use</P> <LI-CODE lang="markup">Growth Rate from Average Sales = VAR CurrentQuarterSales = CALCULATE ( AVERAGE ( Assets[Sales] ), DATESQTD ( 'Date'[Date] ) ) VAR PreviousQuarterSales = CALCULATE ( AVERAGE ( Assets[Sales] ), DATEADD ( DATESQTD ( 'Date'[Date] ), -1, QUARTER ) ) RETURN IF ( ISBLANK ( CurrentQuarterSales ) || ISBLANK ( PreviousQuarterSales ), BLANK (), DIVIDE ( CurrentQuarterSales - PreviousQuarterSales, PreviousQuarterSales ) ) </LI-CODE> Mon, 20 Mar 2023 15:20:07 GMT johnt75 2023-03-20T15:20:07Z Quarterly Growth Calculation https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/Quarterly-Growth-Calculation/m-p/3142603#M112258 <P>Hello!</P><P>&nbsp;</P><P>I'm calculating the average growth rates of some sale values for each quarter. I had to convert the quarters into numeric values to get it to work though.&nbsp;</P><DIV><DIV>&nbsp;</DIV><DIV>Here is the measure I used:</DIV><DIV><P>&nbsp;</P><DIV><DIV><SPAN>Growth Rate from Average Sales = </SPAN></DIV><DIV><SPAN>VAR</SPAN><SPAN> CurrentQuarter = </SPAN><SPAN>MAX</SPAN><SPAN>(Assets[Quarter])</SPAN></DIV><DIV><SPAN>VAR</SPAN><SPAN> CurrentQuarterSales = </SPAN><SPAN>CALCULATE</SPAN><SPAN>(</SPAN><SPAN>AVERAGE</SPAN><SPAN>(Assets[Sales]), Assets[Quarter] = CurrentQuarter)</SPAN></DIV><DIV><SPAN>VAR</SPAN><SPAN> PrevQuarter = </SPAN><SPAN>MAX</SPAN><SPAN>(Assets[Quarter]) - </SPAN><SPAN>1</SPAN></DIV><DIV><SPAN>VAR</SPAN><SPAN> PreviousQuarterSales = </SPAN><SPAN>CALCULATE</SPAN><SPAN>(</SPAN><SPAN>AVERAGE</SPAN><SPAN>(Assets[Sales]), Assets[Quarter] = PrevQuarter)</SPAN></DIV><DIV><SPAN>RETURN</SPAN></DIV><DIV><SPAN>IF</SPAN><SPAN>(</SPAN><SPAN>ISBLANK</SPAN><SPAN>(CurrentQuarterSales) || </SPAN><SPAN>ISBLANK</SPAN><SPAN>(PreviousQuarterSales), </SPAN><SPAN>BLANK</SPAN><SPAN>(), </SPAN><SPAN>DIVIDE</SPAN><SPAN>(CurrentQuarterSales - PreviousQuarterSales, PreviousQuarterSales))</SPAN></DIV></DIV><P>&nbsp;</P><P>Here is the data:</P><TABLE border="0" cellspacing="0" cellpadding="0"><TBODY><TR><TD>Sales</TD><TD>Quarter</TD><TD>Qlabel</TD></TR><TR><TD>454</TD><TD>4</TD><TD>Q4 2022</TD></TR><TR><TD>326</TD><TD>4</TD><TD>Q4 2022</TD></TR><TR><TD>542</TD><TD>3</TD><TD>Q3 2022</TD></TR><TR><TD>542</TD><TD>4</TD><TD>Q4 2022</TD></TR><TR><TD>653</TD><TD>3</TD><TD>Q3 2022</TD></TR><TR><TD>553</TD><TD>2</TD><TD>Q2 2022</TD></TR><TR><TD>535</TD><TD>2</TD><TD>Q2 2022</TD></TR><TR><TD>554</TD><TD>2</TD><TD>Q2 2022</TD></TR><TR><TD>553</TD><TD>1</TD><TD>Q1 2022</TD></TR><TR><TD>985</TD><TD>4</TD><TD>Q4 2022</TD></TR><TR><TD>696</TD><TD>4</TD><TD>Q4 2022</TD></TR><TR><TD>662</TD><TD>4</TD><TD>Q4 2022</TD></TR><TR><TD>366</TD><TD>3</TD><TD>Q3 2022</TD></TR><TR><TD>365</TD><TD>1</TD><TD>Q1 2022</TD></TR><TR><TD>665</TD><TD>4</TD><TD>Q4 2022</TD></TR><TR><TD>363</TD><TD>3</TD><TD>Q3 2022</TD></TR><TR><TD>352</TD><TD>1</TD><TD>Q1 2022</TD></TR><TR><TD>233</TD><TD>4</TD><TD>Q4 2022</TD></TR><TR><TD>352</TD><TD>1</TD><TD>Q1 2022</TD></TR><TR><TD>524</TD><TD>2</TD><TD>Q2 2022</TD></TR><TR><TD>235</TD><TD>4</TD><TD>Q4 2022</TD></TR><TR><TD>241</TD><TD>3</TD><TD>Q3 2022</TD></TR><TR><TD>545</TD><TD>1</TD><TD>Q1 2022</TD></TR><TR><TD>658</TD><TD>3</TD><TD>Q3 2022</TD></TR><TR><TD>654</TD><TD>4</TD><TD>Q4 2022</TD></TR><TR><TD>354</TD><TD>3</TD><TD>Q3 2022</TD></TR><TR><TD>365</TD><TD>4</TD><TD>Q4 2022</TD></TR><TR><TD>1000</TD><TD>1</TD><TD>Q1 2022</TD></TR><TR><TD>545</TD><TD>3</TD><TD>Q3 2022</TD></TR><TR><TD>985</TD><TD>2</TD><TD>Q2 2022</TD></TR><TR><TD>654</TD><TD>1</TD><TD>Q1 2022</TD></TR><TR><TD>354</TD><TD>1</TD><TD>Q1 2022</TD></TR></TBODY></TABLE><P>&nbsp;</P><P>When I turn it into a visualisation it shows the quarter as a number (1 instead of Q1 2022). <U>Is there any way of calculating the growth and then in the visualisation showing the % growth against the quarter as written in the Qlabel column (Q1 2022)?</U></P><P>&nbsp;</P><P>Here is the visualisation currently. the row marked as "4" is the growth from Q3 2022 to Q4 2022.</P><img /><P>&nbsp;</P><BR /><P>&nbsp;</P></DIV></DIV> Mon, 20 Mar 2023 14:13:43 GMT https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/Quarterly-Growth-Calculation/m-p/3142603#M112258 mapman1980 2023-03-20T14:13:43Z Re: Quarterly Growth Calculation https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/Quarterly-Growth-Calculation/m-p/3142786#M112275 <P>Set up a proper date table, marked as a date table, and you can use</P> <LI-CODE lang="markup">Growth Rate from Average Sales = VAR CurrentQuarterSales = CALCULATE ( AVERAGE ( Assets[Sales] ), DATESQTD ( 'Date'[Date] ) ) VAR PreviousQuarterSales = CALCULATE ( AVERAGE ( Assets[Sales] ), DATEADD ( DATESQTD ( 'Date'[Date] ), -1, QUARTER ) ) RETURN IF ( ISBLANK ( CurrentQuarterSales ) || ISBLANK ( PreviousQuarterSales ), BLANK (), DIVIDE ( CurrentQuarterSales - PreviousQuarterSales, PreviousQuarterSales ) ) </LI-CODE> Mon, 20 Mar 2023 15:20:07 GMT https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/Quarterly-Growth-Calculation/m-p/3142786#M112275 johnt75 2023-03-20T15:20:07Z Re: Quarterly Growth Calculation https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/Quarterly-Growth-Calculation/m-p/3144271#M112404 <P>Thank you very much, that works perfectly and just as intended.</P> Tue, 21 Mar 2023 09:50:35 GMT https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/Quarterly-Growth-Calculation/m-p/3144271#M112404 mapman1980 2023-03-21T09:50:35Z