Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi!
I want to make a forecast based on some different calculated columns.
My starting point is the number of Closed cases per day. This is a simple logic.
Next, I calculate the Rolling average of closed cases per employee. Dax below:
Closed_cases_on_date_person rolling average = VAR __LAST_DATE = LASTDATE('Dates'[Date]) RETURN AVERAGEX( DATESBETWEEN( 'Dates'[Date]; DATEADD(__LAST_DATE; -30; DAY); __LAST_DATE ); CALCULATE(AVERAGE(Dates[Closed_Per_Person]) ))+0
Using this, I forecast the upcoming number of total cases that will be closed:
Closed_cases_on_date_forecast = IF(Dates[Date] <= TODAY();CALCULATE ( DISTINCTCOUNT ( UserActivity[ID] ); FILTER ( UserActivity; UserActivity[To status] in {"Closed"; "Canceled"} && DateValue(UserActivity[EndDate]) = Dates[Date] ) )+0; Dates[Closed_cases_on_date_person rolling average] * [FTEs_Active])
Now I would like to combine this logic. I would like my rolling average to be based on Closed_cases_on_date_forecast and also the other way around. This way, I want to have a longer forecast that automatically uses previously forecasted numbers.
How can I do this?
Solved! Go to Solution.
Hi @Anonymous ,
Please have a try 🙂
Closed_cases_on_date_forecast = VAR __LAST_DATE = LASTDATE ( 'Dates'[Date] ) VAR average = AVERAGEX ( DATESBETWEEN ( 'Dates'[Date]; DATEADD ( __LAST_DATE; -30; DAY ); __LAST_DATE ); CALCULATE ( AVERAGE ( Dates[Closed_Per_Person] ) ) ) + 0 RETURN IF ( Dates[Date] <= TODAY (); CALCULATE ( DISTINCTCOUNT ( UserActivity[ID] ); FILTER ( UserActivity; UserActivity[To status] IN { "Closed"; "Canceled" } && DATEVALUE ( UserActivity[EndDate] ) = Dates[Date] ) ) + 0; average * [FTEs_Active] )
Hi @Anonymous ,
Please have a try 🙂
Closed_cases_on_date_forecast = VAR __LAST_DATE = LASTDATE ( 'Dates'[Date] ) VAR average = AVERAGEX ( DATESBETWEEN ( 'Dates'[Date]; DATEADD ( __LAST_DATE; -30; DAY ); __LAST_DATE ); CALCULATE ( AVERAGE ( Dates[Closed_Per_Person] ) ) ) + 0 RETURN IF ( Dates[Date] <= TODAY (); CALCULATE ( DISTINCTCOUNT ( UserActivity[ID] ); FILTER ( UserActivity; UserActivity[To status] IN { "Closed"; "Canceled" } && DATEVALUE ( UserActivity[EndDate] ) = Dates[Date] ) ) + 0; average * [FTEs_Active] )
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
108 | |
106 | |
87 | |
77 | |
69 |
User | Count |
---|---|
124 | |
112 | |
94 | |
84 | |
75 |