cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
addaline Frequent Visitor
Frequent Visitor

Event In Progress for multi-stage process

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

10 REPLIES 10
Super User
Super User

Re: Event In Progress for multi-stage process

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.

 

Groups.png

 

Regards,

MFelix



Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!




addaline Frequent Visitor
Frequent Visitor

Re: Event In Progress for multi-stage process

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

 

 

CahabaData New Contributor
New Contributor

Re: Event In Progress for multi-stage process

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.

www.CahabaData.com
Super User
Super User

Re: Event In Progress for multi-stage process

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



Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!




addaline Frequent Visitor
Frequent Visitor

Re: Event In Progress for multi-stage process

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,

 

DionSales-Example-Invoice-Report.jpg

 

 

 

 

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

 

 

Super User
Super User

Re: Event In Progress for multi-stage process

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

 



Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!




Highlighted
addaline Frequent Visitor
Frequent Visitor

Re: Event In Progress for multi-stage process

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

Super User
Super User

Re: Event In Progress for multi-stage process

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:

Relations_dates.png

 

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:

Graph_Approve.png

 

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



Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!




addaline Frequent Visitor
Frequent Visitor

Re: Event In Progress for multi-stage process

@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

 

Helpful resources

Announcements
Community News & Announcements

Community News & Announcements

Get your latest community news and announcements.

Summit North America

Power Platform Summit North America

Register by September 5 to save $200

Virtual Launch Event

Microsoft Business Applications Virtual Launch Event

Watch the event on demand for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Users Online
Currently online: 156 members 1,827 guests
Please welcome our newest community members: