Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Mario_Z
Regular Visitor

Measure Forecast Accuracy Several Months

Hello,

 

I'm trying to develop a PowerBI Dashboard to measure the forecast accuracy:

My datastructure is like this in one Table (Several Countries, several Articles and Several Months)

ArticleCountryMonth_salesMonth_ForecastMonth_no 
1DE10020010.2019 
1DE20040011.2019 
1DE60040012.2019 
2DE5010010.2019 
2DE15020011.2019 
2DE30060012.2019 
1JR10020010.2019 

 

If I want to measure the different Forecast KPI it works perfectly for single months:

BIAS (The Difference between Sales and Plan). The measure is: SUM(Table[Month_Forecast]) - Sum(Table[Month_Sales])

MAD(The Sum of the absolute difference of each article line). The measure is = SUMX(Table;ABS(Table[Month_Forecast] - Table[Month_Sales]))

 

If I want to measure the same KPI for several months (in this case the months 10.2019 - 12.2019) I don't know how to measure the MAD:

BIAS ==> The same DAX expression like for single months works perfectly

MAD==> If I am using the same expressions like for single months it shows me a MAD of 900 pcs for country DE (The measue calculates the sum of the ABS difference per each article and month!)

What the measure should do is to calculate the absolute difference per article for the whole selected time range (10.2019 - 12.2019): An Example:

  • Article 1 has a forecast of 1000 pcs for the selected time range in country DE and sales of 900 pcs = MAD of 100 pcs for this article 
  • Article 2 has a forecast of 900 pcs for the selected time range in country DE and sales of 500 pcs = MAD of 400 for this article

==> So the MAD for the country DE in the time range 10.2019 - 12.2019 should be 500 pcs, but I don't know how to generate the Measure.

 

I hope someone can help.

 

Many Thanks in advance,

Mario

2 REPLIES 2
v-eachen-msft
Community Support
Community Support

Hi @Mario_Z ,

 

You could edit your measure like the following DAX:

MAD =
ABS ( SUMX ( ALLSELECTED ( Table ), Table[Month_sales] - Table[Month_Forecast] ) )

 

Community Support Team _ Eads
If this post helps, then please consider Accept it as the solution to help the other members find it.

@v-eachen-msft 

Hi,

 

I think your suggestion solves the problem in the example, but the dax expressions do the same like the BIAS. In my example BIAS and MAD have the same number, so a bad selected example.

 

Maybe a better example with the data below:

 

MAD should be: 1000 =(MAD Article 1 (100) + MAD article 2 (400) + MAD article 3(500))

MAD in time range for article 1 = 1000 - 900 = 100

MAD in time range for article 2 = 900 - 500 = 400

MAD in time range for article 3 = 1500 - 2000 = 500

 

ArticleCountryMonth_salesMonth_forecastMonth_no
1DE100200Okt. 19
1DE200400Nov. 19
1DE600400Dez. 19
2DE50100Okt. 19
2DE150200Nov. 19
2DE300600Dez. 19
3DE800500Okt. 19
3DE200800Nov. 19
3DE1000200Dez. 19

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.