Date diff/ clearance weeks between values in same column help

[Anonymous]

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! 

[lbendlin]

You can flip your measure formula around, using >  and ASC .

Please provide sanitized sample data that fully covers your issue. I can only help you with meaningful sample data.
Please paste the data into a table in your post or use one of the file services like OneDrive or Google Drive. Screenshots of your source data are not useful.


https://community.powerbi.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-...
https://community.powerbi.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523

[Anonymous]

Please see attached a link to one drive location with the power BI file within, hopefully this gives you access. The data set contains many rows and comes from a public data set. Therefore I recomend to filter by 'site name' or 'site number'.

Many thanks for your help. 

https://onedrive.live.com/?cid=6EFEC3DA541E39C4&id=6EFEC3DA541E39C4%21105&parId=root&o=OneUp

https://onedrive.live.com/?id=root&cid=6EFEC3DA541E39C4 

[lbendlin]

please check the link. says "access denied"

[Anonymous]

Apologies - please try this link https://1drv.ms/u/s!AsQ5HlTaw_5uaQfvrWhCrganZHU   

[lbendlin · Accepted solution]

see attached.  Your calculated columns could be optimized I'll leave that up to you. The use of EARLIER is no longer recommended. Use variables instead.

[Anonymous]

Hi Ibendlin, 

Many thanks for providing a solution! 

I will work on developing my skills and optimisinng columns.

Again thank you!