Dear experts,
I have some experience with Power BI/DAX but this challenge has proven beyond my skillset.
Requirements:
I am looking to calculate (I would presume via measure), the cumulative days late to the target date an item has accrued by the end of each month until it has been shipped. This is a cumulative measure, so it is measured more than once – the trigger is the end of the month itself.
These are the potential scenarios for a formula at the end of each month:
Once the days late per order has been calculated (Output I), I need to produce 3 more outputs based on the same logic:
- Output II – Total of order qty late reported at the end of each month. Table per order and sum for each month.
- Output III – Total Oder qty late times the number of days late (Output I x Output II). Table per order and sum for each month.
- Output IV – Average days late (Output III / Output II). Table per average of each month.
There is additional information for each order that I would like to use to aggregate and analyze these results. Which I assume can be done with normal PowerBI features.
Where I am so far:
After reading some blogs and watching some videos I have been able to create the first Output at the Order level entering the following information in a Matrix visualization but, as you may know, I cannot do anything with the data.
In the matrix visualization I am using as columns a Dates table that I generated via ‘Dates = CALENDARAUTO()’. In the rows I am entering a unique identifier for each order. And for the values I have created a Measure called ‘Order_Days_late’ where I have attempted to replicate the logic I explained in the first diagrams.
Here is the code for that measure. KEY: cdd_target_date = Target Date, OTD_Date = Actual Date
Order_days_late =
var adl =
calculate(
if(
max('SO and NDD List'[cdd_target_date]) < endofmonth(Dates[Date]) &&
max('SO and NDD List'[OTD_Date]) > endofmonth(previousmonth(Dates[Date])) &&
max('SO and NDD List'[OTD_Date]) < endofmonth(Dates[Date])
, (max('SO and NDD List'[OTD_Date])-max('SO and NDD List'[cdd_target_date])),
if(
max('SO and NDD List'[cdd_target_date]) < endofmonth(Dates[Date]) &&
(max('SO and NDD List'[OTD_Date]) = blank() || max('SO and NDD List'[OTD_Date]) > endofmonth(Dates[Date]))
,(ENDOFMONTH(Dates[Date])-max('SO and NDD List'[cdd_target_date]))
,blank()
)
)
, 'SO and NDD List'[OTD] = "N")
return
format(adl,0.00)
Any help would be strongly appreciated.
Thank you in advance for sharing the knowledge,
Hi， @RolandoLara
I really want to help you, and it is not hard to calculate what you want, but your result graph seems to come from excel, which is somewhat different from the matrix of powerbi. Could you share your desired result in PowerBI?
Best Regards
Janey Guo
These are the visualizations I am looking for. I made all my calculations in excel except of the last one - which is a measure. I had to create a column for a value for each month end, which is what I am trying to avoid - it is not sustainable. Let me know if this is what you need to provide some help. Thanks!
Helpful resources
Microsoft Fabric Learn Together
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Power BI Monthly Update - April 2024
Check out the April 2024 Power BI update to learn about new features.
Fabric Community Update - April 2024
Find out what's new and trending in the Fabric Community.
