Forum Discussion

gustavo_acqio's avatar
gustavo_acqio
Frequent Visitor
7 years ago
Solved

Calculating min, max and average time between two occurances

Hello,

 

I have a table with date and store, and i want to calculate the time between those two dates in a way that i can get min, max and average days between the two registers.

 

For instance, i had an accident in Store A at 01/01/2019, and then the store proceded to have another accident day 05/01/2019, it went 3 days without accidents but since it can happen multiple times i want to calculate min, max and average to give statistic data.

 

The table is as follows (Sample)

 

I would probably have to store that in another related table, a store table for example, but i think i can do that on my own.

 

I found a solution online, but it doesnt fit my data model, so if you guys could help me out with this one it would be much appreciated.

Need Help
  • v-xicai's avatar
    v-xicai
    7 years ago

    Hi gustavo_acqio ,

     

    Firstly, create columns Group rank, DateDiff to get days between two accident date for each store.

     

    Group rank = RANKX(FILTER(Table1,Table1[Store]=EARLIER(Table1[Store])),Table1[Date],,ASC,Dense)

     

    DateDiff =

    var pdate= CALCULATE(MAX(Table1[Date]),FILTER(Table1,Table1[Store]=EARLIER(Table1[Store])&&Table1[Group rank]=EARLIER(Table1[Group rank])-1))

    return

    DATEDIFF(pdate,Table1[Date],DAY)

     

    Then, create columns to get min, max and average time between two occurances for each store.

     

    Min_days = CALCULATE(MIN(Table1[DateDiff]),FILTER(ALLSELECTED(Table1),Table1[Store]=EARLIER(Table1[Store])))

     

    Max_days = CALCULATE( MAX(Table1[DateDiff]),FILTER(ALLSELECTED(Table1),Table1[Store]=EARLIER(Table1[Store])))

     

    Avg_days = CALCULATE( AVERAGE(Table1[DateDiff]),FILTER(ALLSELECTED(Table1),Table1[Store]=EARLIER(Table1[Store])))

     

    When make some selections in slicer, return result showing picture below.

     

     

     

     

     

     

     

     

     

     

     

     

    Here is my test pbix: https://qiuyunus-my.sharepoint.com/:u:/g/personal/pbipro_qiuyunus_onmicrosoft_com/EaABQf0lJCZKssZatFErw_gBo6wAFMnFtIfPUp-EYeU-ww?e=M6JkPk

     

    Best Regards,

    Amy

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

2 Replies

  • v-xicai's avatar
    v-xicai
    Icon for Community Support rankCommunity Support
    7 years ago

    Hi gustavo_acqio ,

     

    Firstly, create columns Group rank, DateDiff to get days between two accident date for each store.

     

    Group rank = RANKX(FILTER(Table1,Table1[Store]=EARLIER(Table1[Store])),Table1[Date],,ASC,Dense)

     

    DateDiff =

    var pdate= CALCULATE(MAX(Table1[Date]),FILTER(Table1,Table1[Store]=EARLIER(Table1[Store])&&Table1[Group rank]=EARLIER(Table1[Group rank])-1))

    return

    DATEDIFF(pdate,Table1[Date],DAY)

     

    Then, create columns to get min, max and average time between two occurances for each store.

     

    Min_days = CALCULATE(MIN(Table1[DateDiff]),FILTER(ALLSELECTED(Table1),Table1[Store]=EARLIER(Table1[Store])))

     

    Max_days = CALCULATE( MAX(Table1[DateDiff]),FILTER(ALLSELECTED(Table1),Table1[Store]=EARLIER(Table1[Store])))

     

    Avg_days = CALCULATE( AVERAGE(Table1[DateDiff]),FILTER(ALLSELECTED(Table1),Table1[Store]=EARLIER(Table1[Store])))

     

    When make some selections in slicer, return result showing picture below.

     

     

     

     

     

     

     

     

     

     

     

     

    Here is my test pbix: https://qiuyunus-my.sharepoint.com/:u:/g/personal/pbipro_qiuyunus_onmicrosoft_com/EaABQf0lJCZKssZatFErw_gBo6wAFMnFtIfPUp-EYeU-ww?e=M6JkPk

     

    Best Regards,

    Amy

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.