topic Re: How to prevent double-counting of old forecast months that have since become actuals in Desktop
https://community.powerbi.com/t5/Desktop/How-to-prevent-double-counting-of-old-forecast-months-that-have/m-p/1349628#M579861
<P><LI-USER uid="15194"></LI-USER> , can you share some same data. If they part of one table we can use the first nonblank value based on the version and take the value </P>
<P><A href="https://www.linkedin.com/pulse/five-recent-power-bi-functions-you-should-use-more-often-amit-chandak" target="_blank">https://www.linkedin.com/pulse/five-recent-power-bi-functions-you-should-use-more-often-amit-chandak</A></P>
<P> </P>
<P>Of we can use Switch based on not value from 1 plan to another.</P>
<P> </P>Sun, 06 Sep 2020 11:20:49 GMTamitchandak2020-09-06T11:20:49ZHow to prevent double-counting of old forecast months that have since become actuals
https://community.powerbi.com/t5/Desktop/How-to-prevent-double-counting-of-old-forecast-months-that-have/m-p/1349596#M579853
<P><SPAN>Here's my scenario:</SPAN><BR /><BR /><SPAN>It's September and I have the following financial data in my model:</SPAN><BR /><SPAN>- Actuals (up to and including August)</SPAN><BR /><SPAN>- Forecast 9: forecast data for September onwards</SPAN><BR /><SPAN>- Forecast 7: an older forecast from July, which has the then 'forecast' data for July onwards</SPAN><BR /><BR /><SPAN>Actuals and Forecast 9 splice together perfectly and give a continuous monthly profile.</SPAN><BR /><BR /><SPAN>However, if I want to show Actuals and Forecast 7, both data sets include data for July and August. I want to see the picture as it was then, so need to prevent the Actuals for July and August from displaying.</SPAN><BR /><BR /><SPAN>It gets more complicated as I have many more historical forecasts (Forecast 6, 5, 4, etc.), but the above is the essence of the problem. Also, the actuals are more granular than the forecasts, so they currently sit in separate fact tables.</SPAN><BR /><BR /><SPAN>How is best to prevent the double-counting of old forecast months that have since become actuals too? These double-counted months would be different for each old forecast.</SPAN></P><P><BR /><SPAN>Any help or suggestions would be appreciated.</SPAN><BR /><BR /><SPAN>Thanks,</SPAN><BR /><BR /><SPAN>Adrian</SPAN></P>Sun, 06 Sep 2020 09:51:44 GMThttps://community.powerbi.com/t5/Desktop/How-to-prevent-double-counting-of-old-forecast-months-that-have/m-p/1349596#M579853_Adrian2020-09-06T09:51:44ZRe: How to prevent double-counting of old forecast months that have since become actuals
https://community.powerbi.com/t5/Desktop/How-to-prevent-double-counting-of-old-forecast-months-that-have/m-p/1349628#M579861
<P><LI-USER uid="15194"></LI-USER> , can you share some same data. If they part of one table we can use the first nonblank value based on the version and take the value </P>
<P><A href="https://www.linkedin.com/pulse/five-recent-power-bi-functions-you-should-use-more-often-amit-chandak" target="_blank">https://www.linkedin.com/pulse/five-recent-power-bi-functions-you-should-use-more-often-amit-chandak</A></P>
<P> </P>
<P>Of we can use Switch based on not value from 1 plan to another.</P>
<P> </P>Sun, 06 Sep 2020 11:20:49 GMThttps://community.powerbi.com/t5/Desktop/How-to-prevent-double-counting-of-old-forecast-months-that-have/m-p/1349628#M579861amitchandak2020-09-06T11:20:49ZRe: How to prevent double-counting of old forecast months that have since become actuals
https://community.powerbi.com/t5/Desktop/How-to-prevent-double-counting-of-old-forecast-months-that-have/m-p/1349705#M579882
<P><LI-USER uid="15194"></LI-USER> - So, making some assumptions about things, maybe try:</P>
<LI-CODE lang="markup">Measure =
VAR __Date = MAX('Calendar'[Date])
VAR __ActualsMax = MAXX(ALL('Actuals'),[Date])
RETURN
IF(
__Date > __ActualsMax,
SUMX(__Forecast,[Value]),
SUMX(__Actuals,[Value])
)</LI-CODE>
<P>Honestly though I have no idea what you are trying to do, create a new table, or what. The concept above assumes a seperate date table, you figure out if the date is more or less than the maximum date in your Actuals table, if so, return the forecast number, otherwise return the actuals number. Avoids adding both together.</P>
<P> </P>
<P>Not really enough information to go on, please first check if your issue is a common issue listed here: <A href="https://community.powerbi.com/t5/Community-Blog/Before-You-Post-Read-This/ba-p/1116882" target="_blank">https://community.powerbi.com/t5/Community-Blog/Before-You-Post-Read-This/ba-p/1116882</A><BR /><BR />Also, please see this post regarding How to Get Your Question Answered Quickly: <A href="https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490" target="_blank">https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490</A><BR /><BR />The most important parts are:<BR />1. Sample data as text, use the table tool in the editing bar<BR />2. Expected output from sample data<BR />3. Explanation in words of how to get from 1. to 2.</P>Sun, 06 Sep 2020 12:40:23 GMThttps://community.powerbi.com/t5/Desktop/How-to-prevent-double-counting-of-old-forecast-months-that-have/m-p/1349705#M579882Greg_Deckler2020-09-06T12:40:23ZRe: How to prevent double-counting of old forecast months that have since become actuals
https://community.powerbi.com/t5/Desktop/How-to-prevent-double-counting-of-old-forecast-months-that-have/m-p/1359562#M582714
<P>Thanks for the replies <LI-USER uid="313"></LI-USER> and <LI-USER uid="148838"></LI-USER> . I'll try to give some better info this time <span class="lia-unicode-emoji" title=":slightly_smiling_face:">ðŸ™‚</span></P><P> </P><P>Starting with sample data from my 2 fact tables</P><P>Actuals</P><TABLE><TBODY><TR><TD>Customer</TD><TD>SKU Code</TD><TD>Date</TD><TD>Units amount</TD><TD>Gross Sales amount</TD><TD>Rebates amount</TD><TD>Market</TD><TD>Actuals or Forecast Type</TD></TR><TR><TD>101</TD><TD>95347</TD><TD>01/05/2020</TD><TD>202</TD><TD>600</TD><TD>20</TD><TD>Domestic</TD><TD>Actual</TD></TR><TR><TD>101</TD><TD>95349</TD><TD>01/05/2020</TD><TD>433</TD><TD>1500</TD><TD>90</TD><TD>Domestic</TD><TD>Actual</TD></TR><TR><TD>101</TD><TD>132284</TD><TD>01/05/2020</TD><TD>56</TD><TD>100</TD><TD>39</TD><TD>Domestic</TD><TD>Actual</TD></TR><TR><TD>101</TD><TD>40441</TD><TD>01/06/2020</TD><TD>30</TD><TD>52</TD><TD>6</TD><TD>Domestic</TD><TD>Actual</TD></TR><TR><TD>101</TD><TD>40455</TD><TD>01/06/2020</TD><TD>20</TD><TD>35</TD><TD>15</TD><TD>Domestic</TD><TD>Actual</TD></TR><TR><TD>101</TD><TD>44496</TD><TD>01/06/2020</TD><TD>3</TD><TD>63</TD><TD>14</TD><TD>Domestic</TD><TD>Actual</TD></TR><TR><TD>101</TD><TD>95347</TD><TD>01/07/2020</TD><TD>139</TD><TD>500</TD><TD>88</TD><TD>Domestic</TD><TD>Actual</TD></TR><TR><TD>101</TD><TD>95349</TD><TD>01/07/2020</TD><TD>289</TD><TD>1049</TD><TD>110</TD><TD>Domestic</TD><TD>Actual</TD></TR><TR><TD>101</TD><TD>132284</TD><TD>01/07/2020</TD><TD>56</TD><TD>100</TD><TD>9</TD><TD>Domestic</TD><TD>Actual</TD></TR><TR><TD>101</TD><TD>40441</TD><TD>01/08/2020</TD><TD>78</TD><TD>70</TD><TD>3</TD><TD>Domestic</TD><TD>Actual</TD></TR><TR><TD>101</TD><TD>40455</TD><TD>01/08/2020</TD><TD>34</TD><TD>60</TD><TD>2</TD><TD>Domestic</TD><TD>Actual</TD></TR><TR><TD>101</TD><TD>70063</TD><TD>01/08/2020</TD><TD>30</TD><TD>3</TD><TD>0</TD><TD>Domestic</TD><TD>Actual</TD></TR></TBODY></TABLE><P><SPAN><BR />Forecasts</SPAN></P><TABLE><TBODY><TR><TD>Customer</TD><TD>SKU Code</TD><TD>Date</TD><TD>Units amount</TD><TD>Gross Sales amount</TD><TD>Rebates amount</TD><TD>Market</TD><TD>Actuals or Forecast Type</TD></TR><TR><TD>101</TD><TD>106892</TD><TD>01/06/2020</TD><TD>6274</TD><TD>1900</TD><TD>25</TD><TD>Domestic</TD><TD>Forecast6</TD></TR><TR><TD>101</TD><TD>106892</TD><TD>01/06/2020</TD><TD>6274</TD><TD>1900</TD><TD>25</TD><TD>Domestic</TD><TD>Forecast6</TD></TR><TR><TD>101</TD><TD>106892</TD><TD>01/07/2020</TD><TD>11426</TD><TD>3510</TD><TD>102</TD><TD>Domestic</TD><TD>Forecast6</TD></TR><TR><TD>101</TD><TD>106892</TD><TD>01/07/2020</TD><TD>5863</TD><TD>1992</TD><TD>99</TD><TD>Domestic</TD><TD>Forecast6</TD></TR><TR><TD>101</TD><TD>102156</TD><TD>01/08/2020</TD><TD>582</TD><TD>1982</TD><TD>98</TD><TD>Domestic</TD><TD>Forecast6</TD></TR><TR><TD>101</TD><TD>102156</TD><TD>01/08/2020</TD><TD>582</TD><TD>1982</TD><TD>98</TD><TD>Domestic</TD><TD>Forecast8</TD></TR><TR><TD>101</TD><TD>106892</TD><TD>01/08/2020</TD><TD>15334</TD><TD>4802</TD><TD>224</TD><TD>Domestic</TD><TD>Forecast8</TD></TR><TR><TD>101</TD><TD>106892</TD><TD>01/09/2020</TD><TD>14889</TD><TD>4663</TD><TD>214</TD><TD>Domestic</TD><TD>Forecast6</TD></TR><TR><TD>101</TD><TD>106894</TD><TD>01/09/2020</TD><TD>5477</TD><TD>1582</TD><TD>145</TD><TD>Domestic</TD><TD>Forecast6</TD></TR><TR><TD>101</TD><TD>106894</TD><TD>01/09/2020</TD><TD>5477</TD><TD>1582</TD><TD>145</TD><TD>Domestic</TD><TD>Forecast8</TD></TR><TR><TD>101</TD><TD>106892</TD><TD>01/10/2020</TD><TD>6598</TD><TD>2451</TD><TD>240</TD><TD>Domestic</TD><TD>Forecast6</TD></TR><TR><TD>101</TD><TD>106894</TD><TD>01/10/2020</TD><TD>239</TD><TD>77</TD><TD>2</TD><TD>Domestic</TD><TD>Forecast8</TD></TR><TR><TD>101</TD><TD>102156</TD><TD>01/11/2020</TD><TD>564</TD><TD>1920</TD><TD>19</TD><TD>Domestic</TD><TD>Forecast8</TD></TR><TR><TD>101</TD><TD>102156</TD><TD>01/11/2020</TD><TD>564</TD><TD>1920</TD><TD>20</TD><TD>Domestic</TD><TD>Forecast8</TD></TR></TBODY></TABLE><P> </P><P> </P><P>I want to include values (Units, or Gross Sales, or Rebates) from both tables such that I have a continuous time line of actuals and forecast data, for each forecast type, that I can use in time intelligence measures (YTD, YoY etc.).</P><P> </P><P>I can envisage a measure for each forecast type, say 'Actuals & Forecast 6' that:</P><P> - includes actuals until the forecast starts and then forecast values thereafter. So for Forecast6 (which has forecast values from June), it would have Actuals until May (month 5) then Forecast6 values from June onwards.</P><P> - excludes the Actuals values for months 6, 7 and 8.</P><P> </P><P>However, each month there's a new forecast name, say Forecast9 and I don't want to have to create a new set of measures every month.</P><P> </P><P>So far I have some simple measures, for example:</P><P>Gross Sales = <SPAN class="Keyword">SUM</SPAN><SPAN class="Parenthesis"> (</SPAN><SPAN> Actuals[Gross Sales amount] </SPAN><SPAN class="Parenthesis">)</SPAN><SPAN> + </SPAN><SPAN class="Keyword">SUM</SPAN><SPAN class="Parenthesis"> (</SPAN><SPAN> Forecasts[Gross Sales amount] </SPAN><SPAN class="Parenthesis">)</SPAN></P><P>and:</P><P><SPAN class="Keyword">Gross Sales YTD = CALCULATE</SPAN><SPAN class="Parenthesis"> (</SPAN><SPAN> [<SPAN class="Keyword">Gross</SPAN> Sales], </SPAN><SPAN class="Keyword">DATESYTD</SPAN><SPAN class="Parenthesis"> (</SPAN><SPAN> 'Calendar'[Date] </SPAN><SPAN class="Parenthesis">)</SPAN><SPAN> </SPAN><SPAN class="Parenthesis">)</SPAN></P><P> </P><P>These can be used with a slicer on 'Actuals or Forecast Type' and choosing 'Actuals' and 'Forecast6' for example. Aside from the double count issue already mentioned, there's a limitation with this; I can see the business then asking for a side by side comparison between 'Actuals & Forecast6' and 'Actuals & Forecast7' which I can't show with slicers as it would sum Actuals + Forecast6 + Forecast7.</P><P> </P><P>Maybe my approach/understanding is wrong and there's a better way of doing this - suggestions welcomed! And also any help with the DAX for excluding those overlap Actuals months would be great.</P><P> </P><P>I hope that explains the problem a little better. If not, just let me know what's unclear and I'll try to clarify.</P><P> </P><P>Thanks,</P><P> </P><P>Adrian</P>Wed, 09 Sep 2020 21:29:00 GMThttps://community.powerbi.com/t5/Desktop/How-to-prevent-double-counting-of-old-forecast-months-that-have/m-p/1359562#M582714_Adrian2020-09-09T21:29:00Z