Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Date diff/ clearance weeks between values in same column help

Hi everyone! 

 

I'm still learning DAX and was hoping the clever minds here could help me solve my column issue.

 

I'm trying to code a custom column to calculate the weeks between a treatment at a particular site. I have had success with calculating the clearance weeks using the following code which gives me the results in the table below:

 

clearance weeks =
VAR temp =
    TOPN (
        1,
        FILTER (
            'Barentswatch',
            'Barentswatch'[Site Number] = EARLIER ( 'Barentswatch'[Site Number] )
                && 'Barentswatch'[Treatment week] < EARLIER ( 'Barentswatch'[Treatment week] )),
        [Treatment week], DESC
    )
RETURN
    DATEDIFF ( MINX ( temp, [Treatment week] ), 'Barentswatch'[Treatment week], WEEK)
 

 

However, what I need now is the column 'weeks until next treatment' (highlighted yellow), in which the calculation calculates the weeks until next treatment. This is ultimately because I need to be able to calculate the mean weeks until next treatment per treatment type, so I need the weeks until next treatment value to sit in the same row as the treatment type (essentially I need all the results from 'clearance weeks' to shift up 1 row.

 

Any help really appreciated, thank you! 

Need Help

6 Replies