topic Cumulative grand total &amp; percentage of time gone in DAX Commands and Tips https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/Cumulative-grand-total-amp-percentage-of-time-gone/m-p/1931199#M41929 <P>Hello all.</P><P>I have a challenge that I have been struggling to solve for some time now and I am hoping one or more of you may be able to steer me in the right direction.</P><P>&nbsp;</P><P>I have simplified things somewhat, but the basic premise is that we have budgets which are rolled up to the monthly level, with the budgeted amounts all loaded against the first of each month. Through the course of the month, our finance team want to be able to see and report on how well we are performing against that budget given how far through the month we are. This is only ever viewed as deep as the monthly level, so no issues here. <FONT color="#FF0000">I calculate the % time gone</FONT><FONT color="#FF0000"><FONT color="#000000"> in each month</FONT></FONT> and apply this to the total monthly budgeted figure - there is the assumption that the monthly budget will be split evenly by working days in that calendar month.</P><P>&nbsp;</P><P>The trouble comes when looking at the grand total. The budgeted numbers all sum up nicely, but this is then multiplied by the % of time gone in the <EM>year.</EM> This results in a number which may not reflect the sum of the monthly budgeted numbers. I post examples below, given this sample data set.</P><P>&nbsp;</P><TABLE><TBODY><TR><TD>Date</TD><TD>Type</TD><TD>Amount</TD></TR><TR><TD>01 January 2021</TD><TD>Product</TD><TD>36686</TD></TR><TR><TD>01 February 2021</TD><TD>Product</TD><TD>61274</TD></TR><TR><TD>01 March 2021</TD><TD>Product</TD><TD>52257</TD></TR><TR><TD>01 April 2021</TD><TD>Product</TD><TD>32823</TD></TR><TR><TD>01 May 2021</TD><TD>Product</TD><TD>24308</TD></TR><TR><TD>01 June 2021</TD><TD>Product</TD><TD>13116</TD></TR><TR><TD>01 July 2021</TD><TD>Product</TD><TD>33770</TD></TR><TR><TD>01 August 2021</TD><TD>Product</TD><TD>41911</TD></TR><TR><TD>01 September 2021</TD><TD>Product</TD><TD>16317</TD></TR><TR><TD>01 October 2021</TD><TD>Product</TD><TD>14428</TD></TR><TR><TD>01 November 2021</TD><TD>Product</TD><TD>52476</TD></TR><TR><TD>01 December 2021</TD><TD>Product</TD><TD>58714</TD></TR><TR><TD>01 January 2021</TD><TD>Customer</TD><TD>12934</TD></TR><TR><TD>01 February 2021</TD><TD>Customer</TD><TD>33951</TD></TR><TR><TD>01 March 2021</TD><TD>Customer</TD><TD>24146</TD></TR><TR><TD>01 April 2021</TD><TD>Customer</TD><TD>20684</TD></TR><TR><TD>01 May 2021</TD><TD>Customer</TD><TD>32078</TD></TR><TR><TD>01 June 2021</TD><TD>Customer</TD><TD>47528</TD></TR><TR><TD>01 July 2021</TD><TD>Customer</TD><TD>17419</TD></TR><TR><TD>01 August 2021</TD><TD>Customer</TD><TD>38909</TD></TR><TR><TD>01 September 2021</TD><TD>Customer</TD><TD>57259</TD></TR><TR><TD>01 October 2021</TD><TD>Customer</TD><TD>37667</TD></TR><TR><TD>01 November 2021</TD><TD>Customer</TD><TD>18932</TD></TR><TR><TD>01 December 2021</TD><TD>Customer</TD><TD>62019</TD></TR><TR><TD>01 January 2021</TD><TD>Overheads</TD><TD>44289</TD></TR><TR><TD>01 February 2021</TD><TD>Overheads</TD><TD>12181</TD></TR><TR><TD>01 March 2021</TD><TD>Overheads</TD><TD>55487</TD></TR><TR><TD>01 April 2021</TD><TD>Overheads</TD><TD>37080</TD></TR><TR><TD>01 May 2021</TD><TD>Overheads</TD><TD>35556</TD></TR><TR><TD>01 June 2021</TD><TD>Overheads</TD><TD>63492</TD></TR><TR><TD>01 July 2021</TD><TD>Overheads</TD><TD>63065</TD></TR><TR><TD>01 August 2021</TD><TD>Overheads</TD><TD>61488</TD></TR><TR><TD>01 September 2021</TD><TD>Overheads</TD><TD>39400</TD></TR><TR><TD>01 October 2021</TD><TD>Overheads</TD><TD>60279</TD></TR><TR><TD>01 November 2021</TD><TD>Overheads</TD><TD>28180</TD></TR><TR><TD>01 December 2021</TD><TD>Overheads</TD><TD>56302</TD></TR></TBODY></TABLE><P>&nbsp;</P><P>The dates table contains a flag [Working Day] to indicate whether a day is a weekday or a weekend - we are only interested in weekdays for the purposes of this calculation.</P><P>&nbsp;</P><LI-CODE lang="markup">% Time Gone = CALCULATE ( SUM ( [Working Day] ), FILTER ( Dates, Dates[Date] &lt; TODAY () ) ) / CALCULATE ( SUM ( Dates[Working Day] ) )</LI-CODE><LI-CODE lang="markup">Amount To Date = CALCULATE ( SUM ( Budgets[Amount] ) * [% Time Gone] )</LI-CODE><P>&nbsp;</P><P><img /></P><P>&nbsp;</P><P>As you can see, the 'Amount to Date' is calculated correctly for each month.</P><P>The grand total shows 49% of 1,398,405 = 685,807.82; this should be the sum of 'Amount to Date' by month = 634,227.45.</P><P>I understand why this is being calculated in this way, and I understand the concept of HASONEFILTER. My question is how to achieve this assuming that loading the budgets at a daily level is not possible.</P><P>&nbsp;</P><P>I have tried the below formula, but this does not work correctly. I believe I know why - because the [% Time Gone] measure is being evaluated at the row level? And it cannot be evaluated where the first of the month lands on a weekend.</P><P>Anyway, it hasn't really got me any closer to my desired result.</P><P>&nbsp;</P><LI-CODE lang="markup">Amount To Date 2 = SUMX ( Budgets, CALCULATE ( SUM ( Budgets[Amount] ) * [% Time Gone] ) )</LI-CODE><P><img /></P><P>&nbsp;</P><P>I am thinking it would require me to calculate a table with the budgeted numbers being split by working day before doing a SUMX, but after much trying I am no closer to figuring it out.</P><P>&nbsp;</P><P>Any help at all would be much appreciated!</P> Wed, 30 Jun 2021 15:24:02 GMT obrient 2021-06-30T15:24:02Z Cumulative grand total & percentage of time gone https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/Cumulative-grand-total-amp-percentage-of-time-gone/m-p/1931199#M41929 <P>Hello all.</P><P>I have a challenge that I have been struggling to solve for some time now and I am hoping one or more of you may be able to steer me in the right direction.</P><P>&nbsp;</P><P>I have simplified things somewhat, but the basic premise is that we have budgets which are rolled up to the monthly level, with the budgeted amounts all loaded against the first of each month. Through the course of the month, our finance team want to be able to see and report on how well we are performing against that budget given how far through the month we are. This is only ever viewed as deep as the monthly level, so no issues here. <FONT color="#FF0000">I calculate the % time gone</FONT><FONT color="#FF0000"><FONT color="#000000"> in each month</FONT></FONT> and apply this to the total monthly budgeted figure - there is the assumption that the monthly budget will be split evenly by working days in that calendar month.</P><P>&nbsp;</P><P>The trouble comes when looking at the grand total. The budgeted numbers all sum up nicely, but this is then multiplied by the % of time gone in the <EM>year.</EM> This results in a number which may not reflect the sum of the monthly budgeted numbers. I post examples below, given this sample data set.</P><P>&nbsp;</P><TABLE><TBODY><TR><TD>Date</TD><TD>Type</TD><TD>Amount</TD></TR><TR><TD>01 January 2021</TD><TD>Product</TD><TD>36686</TD></TR><TR><TD>01 February 2021</TD><TD>Product</TD><TD>61274</TD></TR><TR><TD>01 March 2021</TD><TD>Product</TD><TD>52257</TD></TR><TR><TD>01 April 2021</TD><TD>Product</TD><TD>32823</TD></TR><TR><TD>01 May 2021</TD><TD>Product</TD><TD>24308</TD></TR><TR><TD>01 June 2021</TD><TD>Product</TD><TD>13116</TD></TR><TR><TD>01 July 2021</TD><TD>Product</TD><TD>33770</TD></TR><TR><TD>01 August 2021</TD><TD>Product</TD><TD>41911</TD></TR><TR><TD>01 September 2021</TD><TD>Product</TD><TD>16317</TD></TR><TR><TD>01 October 2021</TD><TD>Product</TD><TD>14428</TD></TR><TR><TD>01 November 2021</TD><TD>Product</TD><TD>52476</TD></TR><TR><TD>01 December 2021</TD><TD>Product</TD><TD>58714</TD></TR><TR><TD>01 January 2021</TD><TD>Customer</TD><TD>12934</TD></TR><TR><TD>01 February 2021</TD><TD>Customer</TD><TD>33951</TD></TR><TR><TD>01 March 2021</TD><TD>Customer</TD><TD>24146</TD></TR><TR><TD>01 April 2021</TD><TD>Customer</TD><TD>20684</TD></TR><TR><TD>01 May 2021</TD><TD>Customer</TD><TD>32078</TD></TR><TR><TD>01 June 2021</TD><TD>Customer</TD><TD>47528</TD></TR><TR><TD>01 July 2021</TD><TD>Customer</TD><TD>17419</TD></TR><TR><TD>01 August 2021</TD><TD>Customer</TD><TD>38909</TD></TR><TR><TD>01 September 2021</TD><TD>Customer</TD><TD>57259</TD></TR><TR><TD>01 October 2021</TD><TD>Customer</TD><TD>37667</TD></TR><TR><TD>01 November 2021</TD><TD>Customer</TD><TD>18932</TD></TR><TR><TD>01 December 2021</TD><TD>Customer</TD><TD>62019</TD></TR><TR><TD>01 January 2021</TD><TD>Overheads</TD><TD>44289</TD></TR><TR><TD>01 February 2021</TD><TD>Overheads</TD><TD>12181</TD></TR><TR><TD>01 March 2021</TD><TD>Overheads</TD><TD>55487</TD></TR><TR><TD>01 April 2021</TD><TD>Overheads</TD><TD>37080</TD></TR><TR><TD>01 May 2021</TD><TD>Overheads</TD><TD>35556</TD></TR><TR><TD>01 June 2021</TD><TD>Overheads</TD><TD>63492</TD></TR><TR><TD>01 July 2021</TD><TD>Overheads</TD><TD>63065</TD></TR><TR><TD>01 August 2021</TD><TD>Overheads</TD><TD>61488</TD></TR><TR><TD>01 September 2021</TD><TD>Overheads</TD><TD>39400</TD></TR><TR><TD>01 October 2021</TD><TD>Overheads</TD><TD>60279</TD></TR><TR><TD>01 November 2021</TD><TD>Overheads</TD><TD>28180</TD></TR><TR><TD>01 December 2021</TD><TD>Overheads</TD><TD>56302</TD></TR></TBODY></TABLE><P>&nbsp;</P><P>The dates table contains a flag [Working Day] to indicate whether a day is a weekday or a weekend - we are only interested in weekdays for the purposes of this calculation.</P><P>&nbsp;</P><LI-CODE lang="markup">% Time Gone = CALCULATE ( SUM ( [Working Day] ), FILTER ( Dates, Dates[Date] &lt; TODAY () ) ) / CALCULATE ( SUM ( Dates[Working Day] ) )</LI-CODE><LI-CODE lang="markup">Amount To Date = CALCULATE ( SUM ( Budgets[Amount] ) * [% Time Gone] )</LI-CODE><P>&nbsp;</P><P><img /></P><P>&nbsp;</P><P>As you can see, the 'Amount to Date' is calculated correctly for each month.</P><P>The grand total shows 49% of 1,398,405 = 685,807.82; this should be the sum of 'Amount to Date' by month = 634,227.45.</P><P>I understand why this is being calculated in this way, and I understand the concept of HASONEFILTER. My question is how to achieve this assuming that loading the budgets at a daily level is not possible.</P><P>&nbsp;</P><P>I have tried the below formula, but this does not work correctly. I believe I know why - because the [% Time Gone] measure is being evaluated at the row level? And it cannot be evaluated where the first of the month lands on a weekend.</P><P>Anyway, it hasn't really got me any closer to my desired result.</P><P>&nbsp;</P><LI-CODE lang="markup">Amount To Date 2 = SUMX ( Budgets, CALCULATE ( SUM ( Budgets[Amount] ) * [% Time Gone] ) )</LI-CODE><P><img /></P><P>&nbsp;</P><P>I am thinking it would require me to calculate a table with the budgeted numbers being split by working day before doing a SUMX, but after much trying I am no closer to figuring it out.</P><P>&nbsp;</P><P>Any help at all would be much appreciated!</P> Wed, 30 Jun 2021 15:24:02 GMT https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/Cumulative-grand-total-amp-percentage-of-time-gone/m-p/1931199#M41929 obrient 2021-06-30T15:24:02Z Re: Cumulative grand total & percentage of time gone https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/Cumulative-grand-total-amp-percentage-of-time-gone/m-p/1931343#M41939 <P><img /></P><P>&nbsp;</P><P><A href="https://www.dropbox.com/s/nmz7680rasn2o13/obrient.pbix?dl=0" target="_self">https://www.dropbox.com/s/nmz7680rasn2o13/obrient.pbix?dl=0</A>&nbsp;</P><P>&nbsp;</P><P>&nbsp;</P> Wed, 30 Jun 2021 16:36:10 GMT https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/Cumulative-grand-total-amp-percentage-of-time-gone/m-p/1931343#M41939 Jihwan_Kim 2021-06-30T16:36:10Z