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.
- Anonymous7 years agoNot applicable
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 !