Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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]
Solved! Go to 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.
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.
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.
User | Count |
---|---|
50 | |
24 | |
19 | |
18 | |
14 |
User | Count |
---|---|
91 | |
86 | |
39 | |
25 | |
22 |