Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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
Solved! Go to 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.
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.
User | Count |
---|---|
124 | |
106 | |
99 | |
63 | |
59 |
User | Count |
---|---|
135 | |
116 | |
101 | |
71 | |
61 |