Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
2 years ago
Solved

Calculating running days without a accident

Hello community, I need help with a health safety dashboard at work.

I can't calculate accident-free days.

I have a table of accident records, you can see it below:

I want to calculate the accumulated number of days without accidents if it is an "AT" accident.

 

DataTipo de Evento
06/01/2023AT
11/01/2023AT
15/01/2023AT
09/01/2023PS
26/01/2023PS
27/01/2023PS
27/01/2023PS
31/01/2023PS
05/01/2023QA
16/01/2023QA
25/01/2023PS
17/01/2023QA
16/01/2023QA
24/01/2023QA
  • Dangar332's avatar
    Dangar332
    2 years ago

    hi, Anonymous 


    try below measure

     

    Measure 2 = 
var a = CALCULATE(MIN('Table (2)'[Data]),FILTER(ALL('Table (2)'),'Table (2)'[Tipo de Evento]="at"))
RETURN
SUMX(
    FILTER( 
        'Table (2)',
        [Tipo de Evento]="at" &&
        [Data]<=MAX([Data])
    ),
    DATEDIFF(a,MIN('Table (2)'[Data]),DAY)
)

     

     

     

3 Replies

  • Dangar332's avatar
    Dangar332
    Icon for Resident Rockstar rankResident Rockstar
    2 years ago

    hi, Anonymous 


    try below measure

     

    Measure 2 = 
var a = CALCULATE(MIN('Table (2)'[Data]),FILTER(ALL('Table (2)'),'Table (2)'[Tipo de Evento]="at"))
RETURN
SUMX(
    FILTER( 
        'Table (2)',
        [Tipo de Evento]="at" &&
        [Data]<=MAX([Data])
    ),
    DATEDIFF(a,MIN('Table (2)'[Data]),DAY)
)

     

     

     

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hi Dangar332 

    Thank you very much for your solution, however I found a solution, which is not perfect. It is not calculating before the first event. How can you see.

     

     

     

    • Dangar332's avatar
      Dangar332
      Icon for Resident Rockstar rankResident Rockstar
      2 years ago

      hi, Anonymous 

       

      in your var data _anteior_acidente  you count previous data of  Tipo de Evento="AT" of current date of "at"

      means currentdate :15/01/2023  and  var data _anteior_acidente is calculate its previous value of "at" that is 11/1/2023

       

      update  var data _anteior_acidente =
      calculate(
           min(tablename[data]),
           tablename[data]<=data_currente,
           tablename[tipo de evento]="at"
        ) 

       

      you get your answer