Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

Calculate date difference for a specific column value+ exclude values

Hello all,  I'm stuck with some calculation and I could use some help.  Here's what I'm trying to do :  I have a production table with all my production orders and I would like to calculate the...
  • Anonymous's avatar
    Anonymous
    7 years ago

    Ok thank you for your reply, I manage to get the measure I expected from that topic. 

    So the first calculated column gives me the date of the last production for each product group row : 

    Date_Previous_Campaign = 
    MAXX (
        TOPN (
            1;
            FILTER (
                TABLE;
               TABLE[Product Group] = EARLIER ( TABLE[Product Group] )
                    && TABLE[DATE] < EARLIER ( TABLE[DATE] )
            );
           TABLE[DATE]; DESC
        );
        TABLE[DATE]
    )

    Then I create a second calculated column to calculate the duration between two production orders : 

    Days since last campaign = DATEDIFF(TABLE[Date_Previous_Campaign];TABLE[DATE];DAY)


    Then eventually my measure of average, excluding values under 3 days to really focus on campaign : 

    Average Duration = CALCULATE(AVERAGE(TABLE[Days since last campaign]);TABLE[Days since last campaign]>3)


    Thanks for your answer, I got the result I expected. However it might not be the most optimized solution. 

    Have a good day !