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
serzott
New Member

Rolling Forecast by month - Forecast accuracy of the year

Hello everybody, 

 

I am new to the community and to Power BI itself, I have been looking for an answer on the already posted questions but I haven't found a solution to my needs. 

 

In my organization we receive a monthly forecast file (units) of what is going to be ordered to our main warehouse. We have then forecast set 01/01/2021, 01/02/2022 an so on. 

I am now working on analysing this data and its accuracy vs the actual sales (or issues from the warehouse in units).

My issue at the moment is I am not able to show in Power BI the correct units I have in my source file, as for one Forecast Set (month in which I receive the forecast) there might be more than one Forecast Date (month in which the units are going to be ordered).


Source file:

MaterialForecast DateForecasted UnitsForecast Set 
4016301/04/202135001/04/2021
4016301/06/20215001/06/2021
4016301/04/202135001/07/2021
4016301/06/20215001/07/2021

 

As you can see above, the total Forecasted Units for 2021 should be 400 for Material 40163, as I am considering what is forecasted for the month (Forecast Date). What I am getting in Power BI is the total sum of everything visible, hence 800 units:

serzott_2-1641487087963.png

 

Additional info:

  • I have a Dates table
    serzott_3-1641487408254.png

     

  • All data for Forecast units and Sales units is coming from a consolidated Forecast table and is working for monthly calculations (Fcst Error, Abs Error, Accuracy, Bias)
    serzott_4-1641487522578.png

     

  • Relationships:
    serzott_5-1641487855645.png

     

  • The forecast source file is at this WeTransfer link:
    https://we.tl/t-T7AjGKBrOF
The question would be what is the best DAX command to use in order to "remove those duplicates"? should I create a new forecast Column or Measure?
Which in this case means having for Material 40163, just 400 units Forecasted. 

 

I apologize in advance if I wasn't clear explaining my issue, and please let me know if I can add any extra info that can be of help. 

 

I thank you for your time. 


Kind regards, 

2 REPLIES 2
v-yetao1-msft
Community Support
Community Support

Hi @serzott 

Has your problem been solved ? If it has been solved, then please consider Accept it as the solution to help the other members find it more quickly.

 

Best Regard

Community Support Team _ Ailsa Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

v-yetao1-msft
Community Support
Community Support

Hi @serzott 

You can use DISTINCT DAX to remove the duplicates . Due to the data you provided is not enough for me to restore the test scenario, you can refer to the links I provided to find a suitable solution.

https://docs.microsoft.com/en-us/dax/distinct-function-dax

https://dax.guide/distinct/

 

Best Regard

Community Support Team _ Ailsa Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

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.

Top Solution Authors