Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago

Need help: generate table and calculated column

Hello,

 

I want to make a comparison between the amount of packages produced (including rejects) by a production line, and the amount of approved packages.

 

The data is comming from 2 different systems, hence I need make sure the timeframe matches between both systems.

 

My idea was to create a calculated column that indicates the production run time.

I want the ProductionDay to start at 05:00 am and end the next day at 04:59 am.

 

Example:

 

ProductionDay    StartTime                     EndTime

1                          28-11-2017 05:00        29-11-2017 04:59

2                          29-11-2017 05:00        30-11-2017 04:59

3                          30-11-2017 05:00        31-11-2017 04:59

 

Now I want to create a calculated column with the ProductionDay in it in my dataset.

 

Could someone explain me a way to generate a table as in the table, and some DAX to get the right productionday in my dataset?

 

All help is appreciated, thanks!

 

 

 

Need Help

2 Replies

  • v-yuta-msft's avatar
    v-yuta-msft
    Community Support
    7 years ago

    Hi Luukv93,

     

    Couldn't find date from 2 different system in your table. Could you show the raw data? In addtion, what's the logic of comparing the amount of packages produced and what's your logic of indicateing the production run time? 

     

    Regards,

    Jimmy Tao

    • Anonymous's avatar
      Anonymous
      Not applicable
      7 years ago

      Hi v-yuta-msft,

       

      The logic is that we work with a system that labels the packages and registers the amount of packages in a SQL-database. The second system follows the SCADA concept and has connection to an ERP system.

       

      The problem basically is that I am encountering differences in amount of packages registered. To a certain level this is acceptable as not all packages come through quality control. But right now I am not able to track this so therefore I need to compare the amount of packages produced between both systems.

       

      Herebelow some sample data from the label machine.

       

       

       

       

      Startzeit and stopzeit are start and endtime. Also included but not shown on the raw data is a unique packaging number (subnumber). On the left you can also see the respective line number.

       

      The next system shows a VISpalletregelID which generated in ERP-system database when a worker enters the amount of colli ready. VISpalletID is the actual pallet on which the colli are stacked. 

       

       

       

      So the problem I am encountering now is that the first labeling system only registers the total quantity of packages produced. The second system generates a PalletID for each pallet produced. So what is happening now is that I want to compare the total quantity produced on a production line from both systems.

       

      So for the labeling system I have the total quantity produced per subnumber and for the ERP-system I have the total quantity produced (based on pallets).

       

      I want to add a calculated column so I can compare the labeling system with the ERP-system in the same timeframe.

      If I don't add a calculated column it is hard to compare the amounts over midnight.

       

      Appreciate your help, let me know if I need to clarify further