Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hi,
Hope someone can help me with this.
I created a P&L and budget in a matrix visual.
I would like to create a P&L rolling forecast in a matrix where the column heads are the months. I have a slicer with the months and if I select January and February it should show the actuals from those two months and the rest should be the budget information.
I followed a video from SQLBI and I got a measure that works, but the problem I have is that the measure is based on the assumption that either budget is empty or actuals is empty. That is not always the case. For example:
- there are no sales yet in March
- an invoice for rent for March has already been received in February and costs are assigned in March.
With the current measure I get the correct information for sales (no actuals so basiscally it's all budget in March. Housing costs however are the total sum of the actuals + budget.
Is there a way to use the slicer in this, so I can cutoff the period?
The formulas currently used are:
RF =
Var LastDateWithSales = calculate(max(Transacties[Rapportagedatum]), REMOVEFILTERS())
Var RemaingForecast =
CALCULATE(
[Begroting],
keepfilters(DimDate[Date]>=LastDateWithSales)
)
Return
RemaingForecast
Sales + forecast = [W&V]+[RF]
@lekkerbek , You can add control, like this example
RF =
Var LastDateWithSales = calculate(max(Transacties[Rapportagedatum]), REMOVEFILTERS())
Var RemaingForecast =
CALCULATE(
[Begroting],
filter(all(DimDate) DimDate[Date]>=LastDateWithSales && DimDate[Date] <= eomonth(LastDateWithSales,3) )
)
Return
RemaingForecast
User | Count |
---|---|
128 | |
108 | |
99 | |
65 | |
62 |
User | Count |
---|---|
136 | |
113 | |
102 | |
71 | |
60 |