Event In Progress for multi-stage process

[addaline]

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:

• Sent To Customer
• Approved
• Ready for Scheduling
• Scheduled
• Ready For Invoicing
• Job Completed

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

[CahabaData]

My suggestion is to create a ApprovalCategory table - and include that in your data model:

Status                                   Category

Sent To Customer                preapproval

Approved                             approved

Ready for Scheduling           approved

Scheduled                            approved

Ready For Invoicing              complete

Job Completed                     complete

I've just invented a couple of categories to give you the idea.  I don't know if this solves everything in your post but in terms of the grouping this is one approach.  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]

Hi @addaline,

Just to be clear you have the status that you need by the date columns that you refer on your first post correct?

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.

If you have some sample data I can try and help you with those measures.

MFelix

[addaline]

Hi @MFelix,

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.

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.

I will try to post some sample data.

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.

Thanks,

Dion

 EDIT: Here is a link to some sample data, https://drive.google.com/file/d/0B5uhF7n9SezLMl9TaEZPWGVxcjQ/view?usp=sharing

[MFelix]

Hi @addaline,

I was abble make the calculations for Approved, Ready and Schedulle projects within the dates you select on the slicers, but in order to give you a correct number how do you want to handle when the dates are similar or within the same month, count as 3 or as 1?

Example:

Proj 1

Approved date = 24/06/2016

Ready for Schedule = 24/06/2016 

Schedule date = 29/06/2016

Proj 2

Approved date = 30/06/2016

Ready for Schedule = 30/06/2016 

Schedule date = 30/06/2016

I calculate based on each date differently you will have 6 project, 2 for each status if separatly you will have 2 projects on because the month is the same for all dates.

Regards,

MFelix

[addaline]

Hi @MFelix,

I was just thinking about that this morning.

It would count as 1, because it is just that one job moving through three different stages of "Approved".

If needed, the last date in which that job is in that cumulative "Approved" state would take priority.

In the Proj 1 example you provide, the "Schedule Date" would take precendence.

Hopefully that helps.

Thanks,

Dion

[MFelix]

Hi @addaline,

I have made some calculation based on your notes however I'm getting to a point were I need some more background information how you want to set up your data. What I have come up until now is this:

1) Make a relationship between your dates and the Calendar table however don't make them active relationships: you should get something like this:

2) Added the measure below accordingly to each of the dates I want to calculate (only show for Approved, Ready for schedulle and Schedulle but for the rest of the dates the formulas are similar):

APPROVED = CALCULATE (
               DISTINCTCOUNT('Internet Sales'[Estimate Number]),
               USERELATIONSHIP('Date'[Date],'Internet Sales'[Approved Date]) )

READY = CALCULATE (
               DISTINCTCOUNT('Internet Sales'[Estimate Number]),
               USERELATIONSHIP('Date'[Date],'Internet Sales'[Ready For Scheduling Date]) )

SCHEDULE = CALCULATE (
               DISTINCTCOUNT('Internet Sales'[Estimate Number]),
               USERELATIONSHIP('Date'[Date],'Internet Sales'[Ready For Scheduling Date]) )

3) Add the measures to the visuals:

This is not yeat set up as you need because I didn't set up the single date for the approved status, my questions are:

- Wich columns correspond to wich status?

- How do you want to handle when the previous columns are in different months, how are the status decided?

Regards,

MFelix

[addaline]

@MFelix

The three dates that need to be combined are:

Approved_Date

Ready_For_Scheduling_Date

Scheduled_Date

If the columns are in a previous month, it will depend on what status we are reporting on.

If I am reporting on all "Sent To Customer" jobs as of April 2017, then any job that has a "Sent_To_Customer_Date" in April 2017 should appear, regardless of its current status and when any of the other dates fields are set to.

If I am reporting on all "Approved" jobs as of April 2017, then all jobs that have a combined "Approved_Date" in April 2017 should appear, regardless of their current status and when any of the other date fields are set to.

To use some pseudo-code based on your Measure example:

SENT = CALCULATE (
               DISTINCTCOUNT('Internet Sales'[Estimate Number]),
               USERELATIONSHIP('Date'[Date],'Internet Sales'[Sent To Customer Date]) )


## How can this part be done? ##
APPROVED = CALCULATE (
               DISTINCTCOUNT('Internet Sales'[Estimate Number]),
               USERELATIONSHIP('Date'[Date],(
                                      'Internet Sales'[Approved Date]
                                      OR 'Internet Sales'[Ready For Scheduling Date]
                                      OR 'Internet Sales'[Scheduled Date])

                ) 
)

INVOICED = CALCULATE (
               DISTINCTCOUNT('Internet Sales'[Estimate Number]),
               USERELATIONSHIP('Date'[Date],'Internet Sales'[Completed Date]) )

Thanks,

Dion

[MFelix]

Hi @addaline,

First of all let me apoligize for just replying today, but busy week.

I think I have made a setup that you can use please try it and then tell me what are the questions that arrive with you setup and the calculation needed to be adjusted.

Create 3 Columns:

All_Approvall_Dates = IF(
			FORMAT( 'Internet Sales'[Approved Date];"mm-yyyy")=FORMAT('Internet Sales'[Ready For Scheduling Date];"mm-yyyy") && 
                        FORMAT('Internet Sales'[Approved Date];"mm-yyyy")=FORMAT('Internet Sales'[Scheduled Date];"mm-yyyy");
                       TRUE();
                       FALSE()
                       ) 

Approval_2_Dates = IF(
                      FORMAT( 'Internet Sales'[Approved Date];"mm-yyyy")=FORMAT('Internet Sales'[Ready For Scheduling Date];"mm-yyyy");
                      TRUE();
                      FALSE()
                      ) 

Approval_Date = SWITCH(
                     TRUE();
                     'Internet Sales'[Approval_2_Dates]='Internet Sales'[All_Approvall_Dates]; 
                          'Internet Sales'[Scheduled Date];
                     'Internet Sales'[All_Approvall_Dates]=FALSE();
                          'Internet Sales'[Ready For Scheduling Date]; 
                     'Internet Sales'[Approved Date]
                     )

With this columns I'm testing if all of the dates are equal and returning the final date (if there as to be any changes in this probably it will have to do with the last column I present.

Them create the non active relationship between the date calendar and the Approval_Date column

Create the following measures and add them to your visuals:

Completed = CALCULATE (
               DISTINCTCOUNT('Internet Sales'[Estimate Number]);
               USERELATIONSHIP('Date'[Date];'Internet Sales'[Completed Date]) )

Sent_To_Customer = CALCULATE (
               DISTINCTCOUNT('Internet Sales'[Estimate Number]);
               USERELATIONSHIP('Date'[Date];'Internet Sales'[Sent To Customer Date])  )

Approved = CALCULATE (
               DISTINCTCOUNT('Internet Sales'[Estimate Number]);
               USERELATIONSHIP('Date'[Date];'Internet Sales'[Approval_Date]) )

 I admit this is a little mesy in terms of final result, I'm still trying to see how I can calculate the approved date amog the tree dates you have, please test it and tell me what you think.

Regards,

MFelix

[MFelix]

Hi @addaline,

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.

Regards,

MFelix

[addaline]

Hi @MFelix,

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.

I've tried grouping by date, but that doesn't seem to allow for multiple date fields.

I tried creating a hierarchy of the dates, but that splits the dates out by day, and I need a monthly total.

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.

Dion