cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
MarWal
Regular Visitor

Running total with actual and forecast based on a adjustable what if-parameter

Hi all, 

 

Apologies beforehand if this question has been answered earlier. 

I have a dataset of cleared checklists (CLs) spanning from 2015 until now, and I want to display the actual RT and the actual + forecast RT. The latter is based on a series generated though the what if parameter, where I can adjust the number of CLs to see at what date the total number of CLs will be reached (around 50'000). The RT for just the actuals works fine, the problem arises when I try to combine it with the forecast RT thus I guess the problem lies with var = RT_Forecast. Also, I don't want the RT to reset per year/month/week, yet I want to display the data per week. 

Moreover, the "Checklists per week" gets multiplied by 7 in the RT_forecast column, which I assume is due to the weekly aggregativ, though I didn't have this issue in an earlier try where I narroved the dataset to only weeks in 2021. 

Below are the calculations I've used so far, any help for coding or structure would be much appreciated! 

 

Checklists per week = GENERATESERIES(0, 200, 1)
Checklists per week Value = SELECTEDVALUE('Checklists per week'[Checklists per week])

Forecast checklists per week =
var LastDateWithCL = CALCULATE(max('Checklists Actual'[Actual Date]),REMOVEFILTERS())
return
if(and(count('Checklists Actual'[Actual Date])=blank(), max('Calendar 1'[Date])>LastDateWithCL), 'Checklists per week'[Checklists per week Value],counta('Checklists Actual'[Actual Date]))
RT_Actual =
var MaxDate = MAX('Calendar 1'[Date])
var RT_Actual =
CALCULATE(
COUNTA('Checklists Actual'[Actual Date]),
FILTER(
ALLSELECTED('Calendar 1'),
'Calendar 1'[Date]<=MaxDate))
return
RT_Actual

RT_Forecast =
var MaxDate = max('Calendar 1'[Date])
var TodayDate = TODAY()
var RT_Actual =
CALCULATE(
COUNTA('Checklists Actual'[Actual Date]),
FILTER(
ALLSELECTED('Calendar 1'),
'Calendar 1'[Date]<=MaxDate))
var RT_Forecast =
CALCULATE(
SUMX(FILTER(ALLSELECTED('Calendar 1'[Date]),'Calendar 1'[Date]<=MaxDate),'Checklists per week'[Checklists per week Value]))
return
if(MaxDate<TodayDate,RT_Actual,RT_Forecast)
MarWal_0-1624437062153.png

 

Best regards,

Marte

0 REPLIES 0

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

July 2021 Update 768x460.png

Check it out!

Click here to read more about the July 2021 Updates