DAX implementation of Holt-Winters Additive - Help working around Recursion
Our users love the forecasts that the Power BI visuals provide. However, we need to be able to see the values of the forecasted values in a Power BI visual, i.e. a table, as well as to be able to do more with those values. Sandeep Pawar has a great post about the underlying methodology used by Power BI: ETS(AAA) https://pawarbi.github.io/blog/forecasting/python/powerbi/forecasting_in_powerbi/2020/04/24/timeseries-powerbi.html#How-does-Power-BI-create-the-forecast? Charles Zaiontz from Real-Statistics.com has excellent working models in Excel of Holt-Winters Additive <-> ETS(AAA) Formulas: https://www.real-statistics.com/time-series-analysis/basic-time-series-forecasting/holt-winters-additive/ Excel file with example (see sheet HoltWinters6): https://www.real-statistics.com/wp-content/uploads/2022/03/Real-Statistics-Time-Series-Examples.xlsx The method is, unfortunately for enthusiastic but (so far) unsuccessful DAX practitioners, *recursive* Having read through all of Greg_Deckler 's writing on working around implementing recursion in DAX, as well as AlexisOlson 's great StackOverflow posts https://stackoverflow.com/questions/61257536/how-to-perform-sum-of-previous-cells-of-same-column-in-powerbi and https://stackoverflow.com/questions/60641059/dax-formula-referencing-itself/60656874#60656874 on closed-form implementations, we're still stuck, with all roads leading to the dreaded circular dependecy error. I was momentarily excited thinking that the new OFFSET() function would help, but the DAX engine wasn't fooled. Help me Greg_Deckler , you're my only hope!
Forecast value
I am trying to find out the forecast alue for the next dates using balow formula but after applying the forecast value it not showi n the next date value it showing collection values dax code Forecast 11 = VAR CurrentMonth = MONTH(MAX('Table'[Date])) VAR CurrentYear = YEAR(MAX('Table'[Date])) VAR LastDateOfMonth = CALCULATE(MAX('Table'[Date]), ALL('Table'), 'Table'[Date] >= DATE(CurrentYear, CurrentMonth, 1), 'Table'[Date] <= EOMONTH(MAX('Table'[Date]), 0)) VAR RemainingDays = LastDateOfMonth - MAX('Table'[Date]) + 1 RETURN IF(ISBLANK(SUM('Table'[Collection])), BLANK(), SUM('Table'[Collection]) * RemainingDays)
Forecasting data based on a % increase
Hi all, very new to DAX coding here, so looking for some help as I'm not sure how to go about this... In short I'm trying to forecast values of a certain "index/seriesID", given 2 things: 1) On one table I have historical data of all indexes (seriesID, date, value) 2) On another table I have amongst other things, the rate at which this index is expected to grow for the next 5 years (5 different columns for each year -> seriesID, Y+1 growth, Y+2 growth, Y+3 growth, etc I would like to have a graph that shows all the historical data and then using last known value, show (ideally in a different colour), what would be the expected growth for this index. What would be the way to calculate this in DAX and then, can these values be shown together in the same graph, while making a distinction between what are facts and what is forecast? Best regards, OScar
Inventory management (actuals and forecast combined)
Hello, I'm trying to create the following table in PowerBI. It should work as following: Jan Feb Mar Apr May Jun Jul Aug Sep Okt Nov Dec 1. Starting Inventory 20.000 25.000 35.000 25.000 35.000 40.000 45.000 55.000 45.000 25.000 45.000 35.000 2. Production 10.000 20.000 15.000 30.000 40.000 50.000 40.000 30.000 40.000 40.000 30.000 25.000 3. Sales (forecast) 5.000 10.000 25.000 20.000 35.000 45.000 30.000 40.000 60.000 20.000 40.000 30.000 4. Closing Inventory 25.000 35.000 25.000 35.000 40.000 45.000 55.000 45.000 25.000 45.000 35.000 30.000 1. Starting inventory = StockCount[StockCount] if the month is in the past and it should be equal to the previous month Closing Inventory for the future. 2. Production = Production[Production] 3. Sales (forecast) = Sales Actuals[sales(actuals)] if the month is in the past and it should be Sales Forecast[sales(forecast)] if the month is currently active or in the future. 4. Closing Inventory = Starting inventory + Production - Sales (forecast) I can't share the PBIX file here yet, since I am a fairly new member. However, I can share the file by WeTransfer: https://we.tl/t-OmfofN6WoQ
Using yesterday's rolling average as forecast for all future working days only DAX
Hi there, I want to forecast investor deposits, using the prior 30 Working Day's Rolling Average from YESTERDAY for all future dates. I only want the forecast to show on 'working' days and I only want the forecast to show for future dates, not for past dates. E.g. if the rolling average for yesterday was $900,000, I want $900,000 to show as the forecast for all working days in the future, only. The next day the rolling average might change to $950,000 and then I would want that as the forecast. This is my measure for the rolling average Rolling 20WD Avg (Deposits) = VAR NumberofDays = 20 VAR MaxWorkingDay = Max('Date'[Working Day Number]) VAR MinWorkingDay = MaxWorkingDay - NumberofDays VAR MaxDate = MAX ('Date'[Date] ) VAR DatesToUse = FILTER( ALL ( 'Date' ), 'Date'[Working Day Number] >= MinWorkingDay && 'Date'[Date] <= MaxDate ) VAR Result = DIVIDE (CALCULATE( [Deposits], DatesToUse ), (NumberofDays + 1) ) Return Result Then I made a measure trying to return the rolling average for yesterday only Rolling 20WD Avg (Deposits) (Yesterday) = CALCULATE([Rolling 20WD Avg (Deposits)], FILTER('Date','Date'[Working Day] -1 )) Then with this measure I am attempting to forecast only for working days, Forecast Deposits = CALCULATE( [Rolling 20WD Avg (Deposits) (Yesterday)] , FILTER('Date','Date'[Working Day] ) ) however it causes my visual to error, saying "Couldn't load the data for this visual", calculation error in measure 'Table'[Rolling 20WD Avg (Deposits) (Yesterday): Cannot convert value 'True' of type Text to type Number. I seem to either be able to get yesterday's working average to show as the forecast for all future days (i.e. weekends and workdays), or the rolling average changing by day into the future, for workdays only, but not both at once. I also can't figure out how to get the forecast to show for only future dates. I don't think I can use Max of Deposit date because not all days have deposits, so if yesterday was blank, I want it to stay blank and not show a forecast until today. I have spent days looking through the community and resources online and am totally stuck, so any help would be greatly appreciated! I've included sample data below: Sample Date Table Date Year Month Month Sort Quarter Month Year Month Year Sort Fiscal Month Day Public Holiday Day in Week Working Day Working Day Number 30/04/2023 2023 April 4 Q2 Apr-23 202304 April Sunday 7 FALSE 1613 29/04/2023 2023 April 4 Q2 Apr-23 202304 April Saturday 6 FALSE 1613 28/04/2023 2023 April 4 Q2 Apr-23 202304 April Friday 5 TRUE 1613 27/04/2023 2023 April 4 Q2 Apr-23 202304 April Thursday 4 TRUE 1612 26/04/2023 2023 April 4 Q2 Apr-23 202304 April Wednesday 3 TRUE 1611 25/04/2023 2023 April 4 Q2 Apr-23 202304 April Tuesday Anzac Day 2 FALSE 1610 24/04/2023 2023 April 4 Q2 Apr-23 202304 April Monday 1 TRUE 1610 23/04/2023 2023 April 4 Q2 Apr-23 202304 April Sunday 7 FALSE 1609 22/04/2023 2023 April 4 Q2 Apr-23 202304 April Saturday 6 FALSE 1609 21/04/2023 2023 April 4 Q2 Apr-23 202304 April Friday 5 TRUE 1609 20/04/2023 2023 April 4 Q2 Apr-23 202304 April Thursday 4 TRUE 1608 19/04/2023 2023 April 4 Q2 Apr-23 202304 April Wednesday 3 TRUE 1607 18/04/2023 2023 April 4 Q2 Apr-23 202304 April Tuesday 2 TRUE 1606 17/04/2023 2023 April 4 Q2 Apr-23 202304 April Monday 1 TRUE 1605 16/04/2023 2023 April 4 Q2 Apr-23 202304 April Sunday 7 FALSE 1604 15/04/2023 2023 April 4 Q2 Apr-23 202304 April Saturday 6 FALSE 1604 14/04/2023 2023 April 4 Q2 Apr-23 202304 April Friday 5 TRUE 1604 13/04/2023 2023 April 4 Q2 Apr-23 202304 April Thursday 4 TRUE 1603 12/04/2023 2023 April 4 Q2 Apr-23 202304 April Wednesday 3 TRUE 1602 11/04/2023 2023 April 4 Q2 Apr-23 202304 April Tuesday 2 TRUE 1601 10/04/2023 2023 April 4 Q2 Apr-23 202304 April Monday Easter Monday 1 FALSE 1600 9/04/2023 2023 April 4 Q2 Apr-23 202304 April Sunday 7 FALSE 1600 8/04/2023 2023 April 4 Q2 Apr-23 202304 April Saturday 6 FALSE 1600 7/04/2023 2023 April 4 Q2 Apr-23 202304 April Friday Good Friday 5 FALSE 1600 6/04/2023 2023 April 4 Q2 Apr-23 202304 April Thursday 4 TRUE 1600 5/04/2023 2023 April 4 Q2 Apr-23 202304 April Wednesday 3 TRUE 1599 4/04/2023 2023 April 4 Q2 Apr-23 202304 April Tuesday 2 TRUE 1598 3/04/2023 2023 April 4 Q2 Apr-23 202304 April Monday 1 TRUE 1597 2/04/2023 2023 April 4 Q2 Apr-23 202304 April Sunday 7 FALSE 1596 1/04/2023 2023 April 4 Q2 Apr-23 202304 April Saturday 6 FALSE 1596 Sample Deposit Data Deposits Date 31/05/2023 30/05/2023 29/05/2023 28/05/2023 27/05/2023 26/05/2023 25/05/2023 24/05/2023 23/05/2023 22/05/2023 21/05/2023 20/05/2023 19/05/2023 18/05/2023 17/05/2023 533,185 16/05/2023 1,501,997 15/05/2023 14/05/2023 13/05/2023 503,046 12/05/2023 710,161 11/05/2023 1,005,275 10/05/2023 1,398,928 9/05/2023 906,827 8/05/2023 7/05/2023 6/05/2023 993,050 5/05/2023 579,540 4/05/2023 1,003,600 3/05/2023 867,650 2/05/2023 667,717 1/05/2023 30/04/2023 29/04/2023 1,308,653 28/04/2023 553,636 27/04/2023 959,788 26/04/2023 25/04/2023 782,730 24/04/2023 23/04/2023 22/04/2023 1,235,900 21/04/2023 622,685 20/04/2023 1,216,950 19/04/2023 1,730,130 18/04/2023 803,400 17/04/2023 16/04/2023 15/04/2023 502,146 14/04/2023 1,035,386 13/04/2023 1,115,310 12/04/2023 859,685 11/04/2023 10/04/2023 9/04/2023 8/04/2023 7/04/2023 578,061 6/04/2023 1,167,161 5/04/2023 578,425 4/04/2023 2,126,646 3/04/2023 2/04/2023 3,049,423 1/04/2023Forecasting this year's daily sales based on last year's percent complete
Hi! I'm trying to create a daily forecast for sales based on last year's percent complete between October and December. In other words, if cumulative sales on this day last year represented 10% complete last year, I want to be able to be able to forecast the total sales we expect to end up with and then, using last year's daily % complete, forecast what daily sales would look like if they followed the same daily % complete as last year. I mocked up some dummy data below and in this Dummy data for daily forecast file. Results would look something like the table below, but going through the end of December. Any help would be greatly appreciated! Month Day This year sales Cumulative this year sales Forecasted Sales Cumulative % 1 Yr Revenue Sales 1 Yr Ago 1-Oct $682,067 $682,067 $682,067 0.0108688 $810,315 2-Oct $577,821 $1,259,888 $1,259,888 0.0192016 $621,249 3-Oct $824,018 $2,083,906 $2,083,906 0.0275767 $624,396 4-Oct $830,449 $2,914,355 $2,914,355 0.0371482 $713,598 5-Oct $725,546 $3,639,901 $3,639,901 0.0499106 $951,491 6-Oct $757,971 $4,397,872 $4,397,872 0.0609925 $826,206 7-Oct $686,591 $5,084,463 $5,084,463 0.0728939 $887,300 8-Oct $607,627 $5,692,090 $5,692,090 0.0859474 $973,196 9-Oct $518,373 $6,210,463 $6,210,463 0.0949903 $674,185 10-Oct $817,283 $7,027,746 $7,027,746 0.1048278 $733,433 11-Oct $879,377 $7,907,123 $7,907,123 0.1146099 $729,291 12-Oct $678,086 $8,585,209 $8,585,209 0.1246514 $748,638 13-Oct $637,021 $9,222,230 $9,222,230 0.1351502 $782,732 14-Oct $662,635 $9,884,865 $9,884,865 0.146409 $839,396 15-Oct $470,992 $10,355,857 $10,355,857 0.1576202 $835,841 16-Oct $522,056 $10,877,913 $10,877,913 0.1662819 $645,769 17-Oct $537,921 $11,415,834 $11,415,834 0.1749805 $648,514 18-Oct $580,689 $11,996,523 $11,996,523 0.1850124 $747,928 19-Oct $133,738 $12,130,261 $12,130,261 0.1941659 $682,427 20-Oct $0 $12,837,372 0.2054844 $843,842 21-Oct $0 $13,526,794 0.2165198 $822,742 22-Oct $0 $14,140,298 0.22634 $732,137 23-Oct $0 $14,576,190 0.2333172 $520,179 24-Oct $0 $15,056,044 0.2409981 $572,649
Get Power BI Forecast in a table
Hello, I'm trying to use actual sales during a pre-order moment to determine how many pieces per item should be ordered. For this I use the forecast function in the Power BI line graph visual which works really great. The only thing is, I can only see the forecasted value while hovering over it with my mouse, which makes it difficult to determine an order amount for a lot of items (about 2000). Also I cant use these values in formulas. My question is; is it possible to create (for example) a table which displays this forecast? The eventual result I want to see is the forecasted sales on a date in the future for a specific item. Here is a fictional simplified dataset that I'm trying to use. The real dataset will use about 2000 items with 98 days of sales and the to be forecasted amount of days is 168. Item Quantity Date item 1 40 1-jul item 1 55 2-jul item 1 38 3-jul item 1 59 4-jul item 1 60 5-jul item 1 59 6-jul item 1 38 7-jul item 1 59 8-jul item 1 48 9-jul item 1 58 10-jul item 1 69 11-jul item 1 48 12-jul item 1 38 13-jul item 1 32 14-jul item 1 52 15-jul item 1 62 17-jul item 1 74 17-jul item 1 43 18-jul item 1 54 19-jul item 1 34 20-jul item 1 65 21-jul item 1 37 22-jul item 1 48 23-jul item 1 61 24-jul item 1 61 25-jul item 1 48 26-jul item 1 37 27-jul item 1 71 28-jul item 1 74 29-jul item 1 82 1-aug item 1 71 3-aug item 1 61 7-aug item 1 65 8-aug item 1 36 10-aug item 1 57 11-aug item 1 86 12-aug item 1 36 13-aug item 1 26 14-aug item 1 100 15-aug item 2 3 1-jul item 2 4 2-jul item 2 5 3-jul item 2 4 4-jul item 2 5 5-jul item 2 0 6-jul item 2 0 7-jul item 2 2 8-jul item 2 0 9-jul item 2 5 10-jul item 2 6 11-jul item 2 4 12-jul item 2 0 13-jul item 2 3 14-jul item 2 2 15-jul item 2 0 16-jul item 2 3 17-jul item 2 3 18-jul item 2 2 19-jul item 2 0 20-jul item 2 4 21-jul item 2 3 22-jul item 2 5 23-jul item 2 0 24-jul item 2 0 25-jul item 2 0 26-jul item 2 6 27-jul item 2 0 28-jul item 2 0 29-jul item 2 2 30-jul item 2 0 31-jul item 2 0 1-aug item 2 0 2-aug item 2 1 3-aug item 2 0 4-aug item 2 0 5-aug item 2 0 6-aug item 2 3 7-aug
