Forum Discussion

Ghaston's avatar
Ghaston
Helper I
8 years ago

Cumulative total - starting date

Hi 
I need to explain the source file first : I have 6 production lines, and each line can only create one product at once. The length of the production time for each line, depends on the product. So i don't have a fixed cycle time. 
I need to run the cumulative total for each line : 
- starting date :  the date where the last product starts being produced in that line
- ending date : today
For exemple, to find the starting date for the cumulative total for the "line 63" : 
- filter the "date-heure" column DESC. 
- filter the " ligne " column = ligne 63.
- Scroll down until you find the line where the "ID_OF" changes. 

 It also happens that for some lines, the "ID_OF" doesn't change ( we only produced 1 product reference during that period). 
For now, i'm using this formula, wich runs the cumulative total but juste while taking into account my time slicer. 


And here is the link to the Excel File that i'm using :
https://drive.google.com/open?id=1Thlz5WV3hA7h223HH3HfCI9Q1WfX6cIL 

 Please help me changing the context of the calculation. 
i'm counting on you guys ! 

 

 

Need Help

3 Replies

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

    Hi Ghaston,

     

    Modify you measure like pattern below and check if it can meet your requirement.

    63 Cuml Arch =
CALCULATE (
    [Entr.Arch.63],
    FILTER (
        SGD_CPT_LIGNE_LIVE_VUE,
        SGD_CPT_LIGNE_LIVE_VUE[Date_Heure] <= MAX ( SGD_CPT_LIGNE_LIVE_VUE[Date_Heure] )
    ),
    ALLSELECTED ( SGD_CPT_LIGNE_LIVE_VUE )
)

     Regards,

    Jimmy Tao

    • Ghaston's avatar
      Ghaston
      Helper I
      8 years ago

      Hi v-yuta-msft

      thanks for the help,  but that doesn't work. 
      In fact i added to the table, a new column, that contains the starting date/time of every product. 
      this column name is "Date Debut". 
      So i need to run the cumulative total from the last starting date/time ( "Date Debut ") in the 63 ligne (" Ligne 63") , to the last date/time (" Date_Heure") in the 63 ligne (" Ligne 63").
      I think that may help to fix the problem.

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

        Hi Ghaston,

         

        Based on your description, maybe the measure is like pattern below:

        63 Cuml Arch =
CALCULATE (
    [Entr.Arch.63],
    FILTER (
        SGD_CPT_LIGNE_LIVE_VUE,
        SGD_CPT_LIGNE_LIVE_VUE[ligne] = "Linge 63"
    ),
    ALLSELECTED ( SGD_CPT_LIGNE_LIVE_VUE )
)

          

        Regards,

        Jimmy Tao