Forecast values in table from Line Chart forecast

[danb]

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

[Anonymous · Accepted solution]

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.

[danb]

@Anonymous  - thank you for the response and the solution. Works great!