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.
I am working on a measure to forecast our number of cases.
The years used are 2017, 2018 and 2019 and I need to forecast the rest of 2019 and 2020.
What I'm basically doing for 2019 is on a day by day basis, I'm taking the cases of 2017+ 2018 divided by 2.
The issue is that for the 2020 forecast, for dates later than today, I don't have 2019 data, so for 2020 I need the averages of 2017, 2018 and 2019 but if the date to forecast in 2020 is later than today in 2019, the calculation is 2017+ 2018 divided by 2.
I hope that this makes sense.
I'm presenting this information in a Matrix.
I am trying the following but I'm not getting the expected results for 2020.
# Cases Forecast = VAR CasesLY = CALCULATE ([# Cases]; DATEADD('Date'[Date]; -1;YEAR)) VAR Cases2YrsAgo = CALCULATE ([# Cases]; DATEADD('Date'[Date]; -2;YEAR)) VAR Cases3YrsAgo = CALCULATE ([# Cases]; DATEADD('Date'[Date]; -3;YEAR)) VAR _year = SELECTEDVALUE('Date'[Calendar Year]) VAR _day = SELECTEDVALUE('Date'[Date]) VAR LYToday = DATE(YEAR(NOW())-1;MONTH(NOW()); DAY(NOW()-1)) RETURN SWITCH( TRUE(); _day >= LYToday && _year = "2020"; DIVIDE(Cases2YrsAgo + Cases3YrsAgo;2;0); _day < LYToday && _year = "2020"; DIVIDE(CasesLY + Cases2YrsAgo + Cases3YrsAgo;3;0); _year = "2019"; DIVIDE(CasesLY + Cases2YrsAgo;2;0) )
I have the feeling that this can be done in a smarter way..
Could somebody give me a hand here?
Solved! Go to Solution.
Hi @setis
Not sure if that is what you are looking for but try this pattern
last three years = VAR _1 = CALCULATE( [Sales], SAMEPERIODLASTYEAR( 'Calendar'[Date] ) ) VAR _2 = CALCULATE( [Sales], SAMEPERIODLASTYEAR( SAMEPERIODLASTYEAR( 'Calendar'[Date] ) ) ) VAR _3 = CALCULATE( [Sales], SAMEPERIODLASTYEAR( SAMEPERIODLASTYEAR( SAMEPERIODLASTYEAR( 'Calendar'[Date] ) ) ) ) VAR _all = { _1, _2, _3 } RETURN SUMX( _all, [Value] ) / COUNTAX( _all, [Value] )
Hi @setis
Not sure if that is what you are looking for but try this pattern
last three years = VAR _1 = CALCULATE( [Sales], SAMEPERIODLASTYEAR( 'Calendar'[Date] ) ) VAR _2 = CALCULATE( [Sales], SAMEPERIODLASTYEAR( SAMEPERIODLASTYEAR( 'Calendar'[Date] ) ) ) VAR _3 = CALCULATE( [Sales], SAMEPERIODLASTYEAR( SAMEPERIODLASTYEAR( SAMEPERIODLASTYEAR( 'Calendar'[Date] ) ) ) ) VAR _all = { _1, _2, _3 } RETURN SUMX( _all, [Value] ) / COUNTAX( _all, [Value] )
@setis You need to get the number of years data that you have. In this case, you have 2017 and 2018. So try to get the DISTINCTCOUNT of year from the data which will become your denominator for future forecasting.
Proud to be a PBI Community Champion
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 |
---|---|
113 | |
99 | |
80 | |
70 | |
59 |
User | Count |
---|---|
149 | |
114 | |
107 | |
89 | |
67 |