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) Best regards, Marte
Measure: Getting running total from previous week number
Hello! I have a measure that is a working running total for every week number and I also have another measure where it would display the last month's running total: Ending Cash is the working running total for every week number. And Beginning Cash should display the last month's Ending Cash. However, I can't display it and I think my computation is wrong. Below is the expected output: Hierarchy 1 2 3 4 Beginning Cash 481975 526139.24 700059.27 512847.74 Ending Cash 526139.24 700059.27 512847.74 438.015.67 Data is sensitive and a lot of computations so I can't share it. Unfortunately, there is no week function on the DATEADD function. My column axis: Week # = WEEKNUM(B[Date].[Date],1) My ending cash calculated measure (running total): Ending Cash Weekly = (SUMX(FILTER(ALLSELECTED('B'[Week #]),'B'[Week #] <= MAX('B'[Week #])),[Net Cash Flow Weekly])) + 'Query - Parameter'[Past Year End Cash Value] My beginning cash calculated measure (the one I have problems with): Beginning Cash Weekly = IF(SELECTEDVALUE('B'[Week #]) = 1, [Past Year End Cash Value], CALCULATE([Ending Cash Weekly], FILTER('B', 'B'[Week #] = SELECTEDVALUE('B'[Week #]) - 1) ) ) My question is, how do I compute for the Beginning Cash which takes the last period's running total? Thanks for the help!
