Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
I am trying to improve an existing report I already have (thanks to this forum for previous help creating it).
We have a production process that runs through 13 different stages. Not all of them are used, but there are key ones we wish to report on:
I understand that this is essentially an "event in progress" kind of problem, but I am not quite grasping how to handle the grouping of 3 stages as 1 stage for the purpose of reporting (Approved, Ready For Scheduling, and Scheduled should all appear in the report as "Approved")
Each stage has a date set for when the job enters that stage. Theoretically the end date could be read as the date of the next stage, but I'm not sure if that will confuse things.
I have created a Date table that includes Fiscal Year information.
I have a main Facts tables that I have created multiple Reference tables from, to handle the various stages. This allowed me to create a relationship for each stage to the Date table. For example:
Date.CalendarDate -> SentToCustomer.SentToCustomerDate
Date.CalendarDate -> Approved.ApprovedDate
Date.CalendarDate -> ReadyForScheduling.ReadyForSchedulingDate
Date.CalendarDate -> Scheduled.ScheduledDate
etc...
My report has a slicer for Fiscal Year, another slicer for Fiscal Month, a third slicer for Sales Rep, and then a bar chart showing the total dollar value of all the estimates by Fiscal Month.
I just can not seem to grasp how to use the event in progress idea to combine "Approved", "Ready For Scheduling" and "Scheduled" items into one "Approved" grouping, so that on my Approved report if I select FY 2017, FM May, I will see all the jobs that were in an "Approved" status at any time in May, regardless of their current status. (This allows us to do year-over-year comparisons)
From Gerhard's post (https://blog.gbrueckl.at/2014/12/events-in-progress-for-time-periods-in-dax/) I think this code segment will help:
[MyOpenOrders_TimePeriod] := CALCULATE ( DISTINCTCOUNT ( 'Internet Sales'[Sales Order Number] ), GENERATE ( VALUES ( 'Date'[Date] ), FILTER ( 'Internet Sales', CONTAINS ( DATESBETWEEN ( 'Date'[Date], 'Internet Sales'[Order Date], 'Internet Sales'[Ship Date] ), [Date], 'Date'[Date] ) ) ) )
But I am not sure how to get it to combine the 3 status dates that make up "Approved".
Am I on the right track, or is there a different method that I should explore?
Thanks in advance.
Dion
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
117 | |
104 | |
77 | |
73 | |
50 |
User | Count |
---|---|
145 | |
109 | |
108 | |
90 | |
64 |