Forum Discussion
Helper IIShipment vs Forecast Accuracy
Hi Team,
1. We have two different tables for Shipment & Forecasted values.(No Keys avaialable hence Appeneded two table)
2. Used
| Brand Code | Customer | Date | Database | Forecast Units | Measure Shipment Units |
| 835341000 | Demo Customer | 1/27/2020 0:00 | Shipment | 81 | |
| 835341000 | Demo Customer | 3/16/2020 0:00 | Shipment | 59 | |
| 835341000 | Demo Customer | 12/16/2019 0:00 | Shipment | 35 | |
| 835341000 | Demo Customer | 1/20/2020 0:00 | Forecast | 60 | 30 |
| 835341000 | Demo Customer | 1/20/2020 0:00 | Shipment | 60 | 30 |
| 835341000 | Demo Customer | 3/9/2020 0:00 | Forecast | 117 | 20 |
| 835341000 | Demo Customer | 3/9/2020 0:00 | Shipment | 117 | 20 |
| 835341000 | Demo Customer | 3/30/2020 0:00 | Shipment | 19 | |
| 835341000 | Demo Customer | 4/6/2020 0:00 | Shipment | 16 | |
| 835341000 | Demo Customer | 12/9/2019 0:00 | Shipment | 11 | |
| 835341000 | Demo Customer | 2/24/2020 0:00 | Shipment | 3 | |
| 835341000 | Demo Customer | 1/13/2020 0:00 | Shipment | 2 | |
| 835341000 | Demo Customer | 3/23/2020 0:00 | Forecast | 0 | 1 |
| 835341000 | Demo Customer | 3/23/2020 0:00 | Shipment | 0 | 1 |
| 835341000 | Demo Customer | 8/19/2019 0:00 | Forecast | 70 | |
| 835341000 | Demo Customer | 12/23/2019 0:00 | Forecast | 110 | |
| 835341000 | Demo Customer | 1/6/2020 0:00 | Forecast | 0 | |
| 835341000 | Demo Customer | 2/10/2020 0:00 | Forecast | 0 | |
| 835341000 | Demo Customer | 5/4/2020 0:00 | Forecast | 0 | |
| 835341000 | Demo Customer | 6/8/2020 0:00 | Forecast | 0 |
I need to Compare forecast Units with Actual Shipments but in the window of 3 weeks.
e.g
Forecast unit present as below as 60 and I need to take some of all shipment units which are availble from 1/20/2020 0:00 to next 3 weeks .
| 1/20/2020 0:00 | Forecast | 60 | 30 |
Could you please help me in this?
Regards,
RSD
Hi RSD ,
In your sample .pbix file, there are missing columns,such as [shipment Units] that I cant directly modify it for you.
I will tell you the steps,pls see below:
Create a calendar table using below dax expression:
calendar table = CALENDAR(MIN('Sheet5'[Master Date]),MAX('Sheet5'[Master Date]))Then modify your measure as below:
Final Shipment = CALCULATE(SUM('Master Table'[Shipment Units]),DATESBETWEEN('Calendar Table'[Date],FIRSTDATE('Calendar Table'[Date]),FIRSTDATE('Calendar Table'[Date])+14))Check whether the above works.
Best Regards,
KellyDid I answer your question? Mark my post as a solution!
8 Replies
- amitchandak
Super User
RSD , Create a date table have week there. Create a Week Rank
Week Start date = 'Date'[Date]+-1*WEEKDAY('Date'[Date],2)+1 // Monday Week -- 'Date'[Date]+-1*WEEKDAY('Date'[Date])+1 //Sunday
Week End date = 'Date'[Date]+ 7-1*WEEKDAY('Date'[Date],2) // Sunday week - 'Date'[Date]+ 7-1*WEEKDAY('Date'[Date])
Week Number = WEEKNUM([Date],2)
Week = if('Date'[Week Number]<10,'Date'[Year]*10 & 'Date'[Week Number],'Date'[Year]&'Date'[Week Number])
Week Rank = RANKX(all('Date'),'Date'[Week Start date],,ASC,Dense)You can get rolling three weeks data like
Last 3 week Sales = CALCULATE(SUM(Sales[Net Sales]),FILTER(all('Date'),'Date'[Week Rank]>=min('Date'[Week Rank])-3 && 'Date'[Week Rank]<=max('Date'[Week Rank])))
Refer this file for example
https://www.dropbox.com/s/d9898a48e76wmvl/sales_analytics_weekWise.pbix?dl=0
To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :
https://radacad.com/creating-calendar-table-in-power-bi-using-dax-functions
https://www.archerpoint.com/blog/Posts/creating-date-table-power-bi
https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/
RSDHi amitchandak ,
Thank you so much for your help but My requirement is whenever I get value for forecast units, I need to Consider that week + 2 consecutive weeks in order to take sum of Shipments unit.Could you please suggest any method for that.
Thank you again:)
Regards,
RSD
v-kelly-msftCommunity Support
Hi RSD ,
You can create a measure like below:
measure=
var _date= calculate(MAX('Table'[date]),dateadd('Table'[date],21,DAY))
Return
Measure Forecast Units = CALCULATE(SUM('Forecast Database'[Units]),filter(all(table),table[date]>=max('table'[date])&&table[date]<=_date),'Forecast Database'[Database]="Forecast")If above doesnt work,pls upload your .pbix file to onedrive business and share the link with us.Do cover the confidential information.
Much appreciated.
Best Regards,
KellyDid I answer your question? Mark my post as a solution!- RSD
Hi v-kelly-msft ,
Thank you for your help.Please find attached PBIX file with sample data.
Somehow I mange to Pull Everything in one table. My requirement is Whenever I find Forecast units then I need to take sum of All Shipment QTY (Only within 3 Weeks with respect to Brand Code and Customer). Could you Please help me with this? Calculated column /Measure both are fine for me.
https://drive.google.com/open?id=1iNCdeMoPPWllkLe9TjL1SBjehvauweb1
- v-kelly-msft
Hi RSD ,
Your link seems not a public one,can you upload your .pbix file to onedrive business and share the link with me?
Much appreciated.
Best Regards,
KellyDid I answer your question? Mark my post as a solution!
