Forum Discussion
Production calendar with quantities
Hello,
I'm trying to make a production calendar with start and end date for each production order and product as well as single and summed quantities. Here is a sample data, similar to the one I want to use:
The visual effect which I'm looking for is something like this:
Thanks!
I've seen a few different approaches to this type of problem, this is one using some M custom columns in Query Editor in PowerBI to generate a 'X Daily Value by Date'. Detailes below by each screen shot:
Thank You,
FOrrest
1: Here's the first code for a Custom Column to divide the Total Production by the number of days between Start and Due. (Adding 1 to Inclusive the 2 dates.)
= [Total_Prod_Qty] / (Duration.Days(Duration.From([Run_Due_Date]-[Run_Start_Date]))+1)
2: Next we need to identify all the individual dates between the Start Date and Due Date. This M code will generate a Custom Column with these dates in LIST format. You can expand this list as shown below to get a ROW for each date between the Start and Due Dates. ** Adding your Daily Total in Stpe 1 FIRST automatilly spreads this value out during this steps. **
= { Number.From([Run_Start_Date])..Number.From([Run_Due_Date])}
3: Format the new Column as a DATE for Power BI to convert the date numbers to a more user friednly format. Now we can start building visuals based on this new Date Custom Column in Power BI
4: The Matrix in this screen shot is cloest to what you where looking to build. The Matrix set-up is shows in the screen shot. I haven't played with colors yet, but it's a start. I also created the stacked bar graph with nicely shows the upcoming drop off of production!
2 Replies
I've seen a few different approaches to this type of problem, this is one using some M custom columns in Query Editor in PowerBI to generate a 'X Daily Value by Date'. Detailes below by each screen shot:
Thank You,
FOrrest
1: Here's the first code for a Custom Column to divide the Total Production by the number of days between Start and Due. (Adding 1 to Inclusive the 2 dates.)
= [Total_Prod_Qty] / (Duration.Days(Duration.From([Run_Due_Date]-[Run_Start_Date]))+1)
2: Next we need to identify all the individual dates between the Start Date and Due Date. This M code will generate a Custom Column with these dates in LIST format. You can expand this list as shown below to get a ROW for each date between the Start and Due Dates. ** Adding your Daily Total in Stpe 1 FIRST automatilly spreads this value out during this steps. **
= { Number.From([Run_Start_Date])..Number.From([Run_Due_Date])}
3: Format the new Column as a DATE for Power BI to convert the date numbers to a more user friednly format. Now we can start building visuals based on this new Date Custom Column in Power BI
4: The Matrix in this screen shot is cloest to what you where looking to build. The Matrix set-up is shows in the screen shot. I haven't played with colors yet, but it's a start. I also created the stacked bar graph with nicely shows the upcoming drop off of production!