Forum Discussion

addaline's avatar
addaline
Helper I
9 years ago

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 ...
Need Help
MFelix's avatar
MFelix
Super User
9 years ago

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's avatar
addaline
Helper I
9 years ago

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's avatar
    MFelix
    Super User
    9 years ago

    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's avatar
      addaline
      Helper I
      9 years ago

      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's avatar
        MFelix
        Super User
        9 years ago

        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