Forum Discussion
Calculate date difference for a specific column value+ exclude values
- Anonymous7 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 !
Anonymous ,
You may check if the post below helps.
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 !