Handing Time Intelligent FCST vs ACT in Power Query/ PowerPivot

[Vaslo]

Hello all,

I am trying to handle an issue with running forecasts. Essentially I drop a bunch of data into a few tables, much of it overlapping in months - meaning that when I drop a new actual month in, I will still keep it's forecasted version in the table and simple use a cell to designate it as actual or fcst (to be honest I may have many forecasts since each month before will be in there, but let's do it simple.)

I designed a table to sort of handle this, but have no idea how to have the table dynamically change, perhaps by changing a cell to let the model know what months should use the actuals and which month should use the latest forecast.

Below is that table but if there is a reference somewhere to explain how to do this, or I am going about this in the wrong way, please let me know. I know a fairly good way to do with just an excel formula, but can't apply it to a Data Model in anyway. In the example, my current Actual month is July 2018, so all the actuals from that point and before should be actual. After that it should use a particular forecast.

Thanks!

[TABLE="width: 297"]
<tbody>[TR]
[TD]Period [/TD]
[TD]CurrentMonth[/TD]
[TD]ScenName[/TD]
[TD] Include?[/TD]
[/TR]
[TR]
[TD]2018-01[/TD]
[TD]2018-07[/TD]
[TD]Actual[/TD]
[TD="align: center"]TRUE[/TD]
[/TR]
[TR]
[TD]2018-02[/TD]
[TD]2018-07[/TD]
[TD]Actual[/TD]
[TD="align: center"]TRUE[/TD]
[/TR]
[TR]
[TD]2018-03[/TD]
[TD]2018-07[/TD]
[TD]Actual[/TD]
[TD="align: center"]TRUE[/TD]
[/TR]
[TR]
[TD]2018-04[/TD]
[TD]2018-07[/TD]
[TD]Actual[/TD]
[TD="align: center"]TRUE[/TD]
[/TR]
[TR]
[TD]2018-05[/TD]
[TD]2018-07[/TD]
[TD]Actual[/TD]
[TD="align: center"]TRUE[/TD]
[/TR]
[TR]
[TD]2018-06[/TD]
[TD]2018-07[/TD]
[TD]Actual[/TD]
[TD="align: center"]TRUE[/TD]
[/TR]
[TR]
[TD]2018-07[/TD]
[TD]2018-07[/TD]
[TD]Actual[/TD]
[TD="align: center"]TRUE[/TD]
[/TR]
[TR]
[TD]2018-08[/TD]
[TD]2018-07[/TD]
[TD]Actual[/TD]
[TD="align: center"]FALSE[/TD]
[/TR]
[TR]
[TD]2018-09[/TD]
[TD]2018-07[/TD]
[TD]Actual[/TD]
[TD="align: center"]FALSE[/TD]
[/TR]
[TR]
[TD]2018-10[/TD]
[TD]2018-07[/TD]
[TD]Actual[/TD]
[TD="align: center"]FALSE[/TD]
[/TR]
[TR]
[TD]2018-11[/TD]
[TD]2018-07[/TD]
[TD]Actual[/TD]
[TD="align: center"]FALSE[/TD]
[/TR]
[TR]
[TD]2018-12[/TD]
[TD]2018-07[/TD]
[TD]Actual[/TD]
[TD="align: center"]FALSE[/TD]
[/TR]
[TR]
[TD]2018-01[/TD]
[TD]2018-07[/TD]
[TD]FCST[/TD]
[TD="align: center"]FALSE[/TD]
[/TR]
[TR]
[TD]2018-02[/TD]
[TD]2018-07[/TD]
[TD]FCST[/TD]
[TD="align: center"]FALSE[/TD]
[/TR]
[TR]
[TD]2018-03[/TD]
[TD]2018-07[/TD]
[TD]FCST[/TD]
[TD="align: center"]FALSE[/TD]
[/TR]
[TR]
[TD]2018-04[/TD]
[TD]2018-07[/TD]
[TD]FCST[/TD]
[TD="align: center"]FALSE[/TD]
[/TR]
[TR]
[TD]2018-05[/TD]
[TD]2018-07[/TD]
[TD]FCST[/TD]
[TD="align: center"]FALSE[/TD]
[/TR]
[TR]
[TD]2018-06[/TD]
[TD]2018-07[/TD]
[TD]FCST[/TD]
[TD="align: center"]FALSE[/TD]
[/TR]
[TR]
[TD]2018-07[/TD]
[TD]2018-07[/TD]
[TD]FCST[/TD]
[TD="align: center"]FALSE[/TD]
[/TR]
[TR]
[TD]2018-08[/TD]
[TD]2018-07[/TD]
[TD]FCST[/TD]
[TD="align: center"]TRUE[/TD]
[/TR]
[TR]
[TD]2018-09[/TD]
[TD]2018-07[/TD]
[TD]FCST[/TD]
[TD="align: center"]TRUE[/TD]
[/TR]
[TR]
[TD]2018-10[/TD]
[TD]2018-07[/TD]
[TD]FCST[/TD]
[TD="align: center"]TRUE[/TD]
[/TR]
[TR]
[TD]2018-11[/TD]
[TD]2018-07[/TD]
[TD]FCST[/TD]
[TD="align: center"]TRUE[/TD]
[/TR]
[TR]
[TD]2018-12[/TD]
[TD]2018-07[/TD]
[TD]FCST[/TD]
[TD="align: center"]TRUE[/TD]
[/TR]
</tbody>[/TABLE]

 

[gazpage]

Well, filters are additive so something like.

CALCULATE (
   SUM ( [Amount] ),
   FILTER ( ALL ( Table ),
       Table[Date] < "31/07/2017",
       [ScenName] = "Actual"
   )
)
+
Repeat the above but switch the sign and change actual to forecast.

Would do it in DAX. Google for disconnected slicers to see how you can control the date to be selectable in your viz.



A better answer would seem to be to only pull in the data you want with Power Query.