Event In Progress for multi-stage process ( Good& Bad QTY %)

[a7med7alim]

Hello Dear Community

I am trying to improve an existing report I already have, We have a production process that runs through 4 different stages:

•  Machine/Casting
• Glazing
• First Fire
• Refire

I know that this is essentially an "event in progress" kind of problem, but I am not quite grasping how to handle the following requirements:

1. How to show the report by all items in one page instead of one item ID .
2. Create the proper measure for Loss % & Accepted QTY & Rejected QTY for each stage.
3. Filter/ slicer by date YTD/MoM.
4. What is the proper Cardinality / modeling.

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 just can not seem to grasp how to use the event in progress idea to combine "Accept", "Reject" and "Loss" grouping, so that on my Accept report if I select FY 2022, FM May, I will see all the items that were in an "Accepted" status at any time in May, regardless of their current status. (This allows us to do year-over-year comparisons).

thanks in advance for your great patient 

https://drive.google.com/drive/folders/1XfpF8tYG1QLEZiXjKEft934GwCpi7EC8?usp=sharing

attached the pbix

[daXtreme]

Hi @a7med7alim 

I've had a look at the file... First thing to notice is that the model is not right. A good model is a star schema where fact tables are connected via conformed dimensions (and this is what PBI wants to see). Your model is basically a set of disconnected tables. Please create a good dimensional model and then everything will be much easier. Also, it's bad practice to have auto-generated date hierarchy in every table that has a date column. Please remove this and create a proper calendar that connects to the right fields in other tables. For the time being this is a model that nobody in their right mind would deploy to production. I'd also advise that you strip the data off the unnecessary attributes or, alternatively, create a junk dimension where you'll store the less used attributes. This will tidy up the model a bit.

Writing measures for the model as it is right now is pointless.