Forum Discussion
Forecast values in table from Line Chart forecast
Good afternoon experts, I have been trying to track down the answer here without luck so figured I would just post and hopefully someone can point me in the right direction. I am trying to put together a forecast based on actual case sales for the previous year. I have the forecast applied to the line chart but would also like to have the forecast values present in a table in the same PBI report. Am I stuck having to write a DAX measure(s) to recreate that in a table or is there a work around?
Here is the test dataset
| Month/Year | NetCases |
| 5/1/2021 0:00 | 3,321,610.32 |
| 6/1/2021 0:00 | 3,334,801.74 |
| 7/1/2021 0:00 | 3,439,256.00 |
| 8/1/2021 0:00 | 3,143,754.72 |
| 9/1/2021 0:00 | 2,975,006.23 |
| 10/1/2021 0:00 | 3,097,979.57 |
| 11/1/2021 0:00 | 3,289,371.60 |
| 12/1/2021 0:00 | 3,300,790.02 |
| 1/1/2022 0:00 | 2,708,015.26 |
| 2/1/2022 0:00 | 2,836,285.04 |
| 3/1/2022 0:00 | 3,356,680.70 |
| 4/1/2022 0:00 | 3,065,062.20 |
| 5/1/2022 0:00 | 3,140,745.66 |
Thanks and sorry again if this is already answered elsewere.
Danb
Hi danb ,
Am I stuck having to write a DAX measure(s) to recreate that in a table or is there a work around?
Yes, you will need to create a measure. And in my workaround, you need to create a new table with whole MonthYear like:
New Table = var _t=ADDCOLUMNS( FILTER( CALENDAR(EDATE( MAX('Table'[Month/Year]),1), EDATE( MAX('Table'[Month/Year]),12) ),DAY([Date])=1),"NetCased",BLANK()) return UNION('Table',_t)Then I used average value as the forecast value for future dates:
Foreast (Average) = IF(SUM('New Table'[NetCases])=BLANK(), CALCULATE(AVERAGE('Table'[NetCases]),FILTER('Table', MONTH([Month/Year])=MONTH(MAX('New Table'[Month/Year])))) , SUM('New Table'[NetCases]))Output:
Best Regards,
Eyelyn Qin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
2 Replies
Hi danb ,
Am I stuck having to write a DAX measure(s) to recreate that in a table or is there a work around?
Yes, you will need to create a measure. And in my workaround, you need to create a new table with whole MonthYear like:
New Table = var _t=ADDCOLUMNS( FILTER( CALENDAR(EDATE( MAX('Table'[Month/Year]),1), EDATE( MAX('Table'[Month/Year]),12) ),DAY([Date])=1),"NetCased",BLANK()) return UNION('Table',_t)Then I used average value as the forecast value for future dates:
Foreast (Average) = IF(SUM('New Table'[NetCases])=BLANK(), CALCULATE(AVERAGE('Table'[NetCases]),FILTER('Table', MONTH([Month/Year])=MONTH(MAX('New Table'[Month/Year])))) , SUM('New Table'[NetCases]))Output:
Best Regards,
Eyelyn Qin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.Anonymous - thank you for the response and the solution. Works great!
