DAX Code not working in PBI Desktop

[Gerald23]

Hi Everyone,

I would like to add 5 working days to a date I already have in my Sales table. To achieve this i have followed the steps described in this video: https://www.youtube.com/watch?v=2HkBbqxBzF0

He describes 2 options the first one works but he explains that the second is better performance wise. So I followed the second one and it gives me the code below which works great in the Dax Studio but when I move the code to the Power BI Desktop file it doesnt.

So how should I change this code so that it works in a new calculated column in the Power bi Desktop?

DEFINE COLUMN 'Date'[WDN] = 
	VAR WorkingDates = 
		CALCULATETABLE(
			Values ( 'Date'[Date]),
			REMOVEFILTERS ( 'Date'),
			'Date'[Working Day] = TRUE
			)
	VAR Result = 
		RANKX ( WorkingDates, 'Date'[Date], , ASC ) - NOT ('Date'[Working Day])
	RETURN
		Result
	COLUMN 'Sales'[WDN+5] =
		VAR CurrentWDN = RELATED( 'Date'[WDN] )
		VAR CurrentPlus5 = CurrentWDN + 5
		VAR Result =
			LOOKUPVALUE(
			'Date'[Date],
			'Date'[WDN], CurrentPlus5,
			'Date'[Working Day], TRUE
			)
		RETURN
			Result
EVALUATE
	ALL ( 'Sales'[Period.Date Value], 'Sales'[WDN+5])
ORDER BY 'Sales'[Period.Date Value]

[Gerald23 · Accepted solution]

So I was able to solve this by splitting the code so that it would make two new calculated columns instead of just one.

First i created a column called WDN using the code below 

WDN = 
	VAR WorkingDates = 
		CALCULATETABLE(
			Values ( 'Date'[Date]),
			REMOVEFILTERS ( 'Date'),
			'Date'[Working Day] = TRUE
			)
	VAR Result = 
		RANKX ( WorkingDates, 'Date'[Date], , ASC ) - NOT ('Date'[Working Day])
	RETURN
		Result

Then i added another calculated column called WDN+5 using this code

WDN+5 = 
		VAR CurrentWDN =  'Date'[WDN]
		VAR CurrentPlus5 = CurrentWDN + 5
		VAR Result =
			LOOKUPVALUE(
			'Date'[Date],
			'Date'[WDN], CurrentPlus5,
			'Date'[Working Day], TRUE
			)
		RETURN
			Result

Now I can use this field in filtering to get the results that are needed for my report.

[daXtreme]

This is a QUERY, not something you can put in PBI just like that. If you want to create a column in a table, then you should extract the pieces that can be used in a calculated column. Certainly, you CANNOT DEFINE anything in a calc column.