Event In Progress for multi-stage process

Question

I am trying to improve an existing report I already have (thanks to this forum for previous help creating it).&nbsp;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:&nbsp;Sent To CustomerApprovedReady for SchedulingScheduledReady For InvoicingJob Completed&nbsp;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")&nbsp;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.&nbsp;I have created a Date table that includes Fiscal Year information.&nbsp;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:&nbsp;Date.CalendarDate -&gt; SentToCustomer.SentToCustomerDateDate.CalendarDate -&gt; Approved.ApprovedDateDate.CalendarDate -&gt; ReadyForScheduling.ReadyForSchedulingDateDate.CalendarDate -&gt; Scheduled.ScheduledDateetc...&nbsp;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.&nbsp;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)&nbsp;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:&nbsp;[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".&nbsp;Am I on the right track, or is there a different method that I should explore?&nbsp;Thanks in advance.&nbsp;Dion

mfelix · Answer

Hi&nbsp;addaline,&nbsp;Did you try do use the Group option you can setup you group according to any field and the use that group on your visuals along with the measures.&nbsp;&nbsp;Regards,MFelix

addaline · Answer

Hi MFelix,&nbsp;Thanks for the suggestion. Unfortunately, the Status is set to the current status of the job, so grouping by Status would only show the jobs that are currently in that state. I need to show the state of the job as of the date selected, regardless of what state it is currently in.&nbsp;I've tried grouping by date, but that doesn't seem to allow for multiple date fields.&nbsp;I tried creating a hierarchy of the dates, but that splits the dates out by day, and I need a monthly total.&nbsp;I *think* I need to create a Measure that looks at the table based on the slicer selections and says "If ApprovedDate OR ReadyForSchedulingDate OR ScheduledDate are in the slicer selection, then add them up", but I am not sure.&nbsp;Dion&nbsp;&nbsp;

cahabadata · Answer

My suggestion is to create a ApprovalCategory table - and include that in your data model:&nbsp;Status&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; CategorySent To Customer&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; preapprovalApproved&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;approvedReady for Scheduling&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; approvedScheduled&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; approvedReady For Invoicing&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; completeJob Completed&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;complete&nbsp;&nbsp;&nbsp;I've just invented a couple of categories to give you the idea.&nbsp; I don't know if this solves everything in your post but in terms of the grouping this is one approach.&nbsp; You can then join your data table field to the status field of this new table - and thereby get the category term whenever you need it.

mfelix · Answer

Hi&nbsp;addaline,&nbsp;Just to be clear you have the status that you need by the date columns that you refer on your first post correct?&nbsp;Do you have a calendar table? If you do you can make several non active relationships between the calendar and you data and use the USERRELATIONSHIP to create the several measures you need for each status that will interact with the data slicer you have.&nbsp;If you have some sample data I can try and help you with those measures.&nbsp;MFelix

addaline · Answer

Hi&nbsp;MFelix,&nbsp;Yes, we have calendar table. I could not figure out the USERREALTIONSHIP, so I created Reference Queries to the main Facts table, and created relationships from each Reference Query to the calendar table. But I am willing to streamline things.&nbsp;The jobs have a status column that shows the current status, but based on the name of the various date columns, I can tell what stage of the process they *were* in.&nbsp;I will try to post some sample data.&nbsp;Attached is a screenshot of the current report layout. I have 3 of them currently that are the same, each dealing with 1 stage in the process: Sent, Approved, Invoiced.&nbsp;Thanks,&nbsp;Dion&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;EDIT: Here is a link to some sample data, https://drive.google.com/file/d/0B5uhF7n9SezLMl9TaEZPWGVxcjQ/view?usp=sharing&nbsp;&nbsp;

Forum Discussion

Event In Progress for multi-stage process

10 Replies

Related content

Progressive Date Columns

Date With Progress

Events in Progress Problem

split, process and recombine data

Track the progression