Forum Discussion
Solved
Date issue
Hi. I have a date column that takes away 9 days. [date] -9, but I would not like to include Saturday and Sunday. Only working days / weekdays.
ea_minus_9 = VAR Period = 9 VAR EndDate = TableName[ea] RETURN IF ( NOT ISBLANK ( EndDate ), VAR StartDate = EndDate - 2 * Period VAR DatesPeriod = CALENDAR ( StartDate, EndDate ) VAR DatesAndWDs = ADDCOLUMNS ( DatesPeriod, "@WorkingDay", VAR DayOfWeek = WEEKDAY ( [Date], 2 ) RETURN IF ( DayOfWeek IN { 6, 7 }, "No", "Yes" ) ) VAR WDsOnly = FILTER ( DatesAndWDs, [@WorkingDay] = "Yes" ) VAR TopNTable = TOPN ( Period + 1, WDsOnly, [Date], DESC ) RETURN MINX ( TopNTable, [Date] ) )
Hi again, so there is no easy way like in Excel with =workdays[date]-9 ??
The columns look like this. If we look at ea_minus_25 2006-11-29. That was a Wednesday, so I if I take -9 it will should be 2006-11-17 because there are 2 weekends. So If I do a Date Table how do I fix this??
EA-MINUS
Hi Bergh
There is no easy way
you can use the following code without the need for the date table
ea_minus_9
VAR Period = 9
VAR EndDate = TableName[ea]
VAR StartDate = EndDate - 2 * Period
VAR DatesPeriod = CALENDAR ( StartDate, EndDate )
VAR DatesAndWDs =
ADDCOLUMNS (
DatesPeriod,
"@WorkingDay",
VAR DayOfWeek = WEEKDAY( [Date], 2)
RETURN
IF ( DayOfWeek In { 6, 7 }, "No", "Yes" )
)
VAR WDsOnly = FILTER ( DatesAndWDs, [@WorkingDay] = "Yes" )
VAR TopNTable = TOPN ( Period + 1, WDsOnly, [Date], DESC )
RETURN
MINX ( TopNTable, [Date] )I had some blank cells, then iI get a error. How do I do to fix this??
ea_minus_9 = VAR Period = 9 VAR EndDate = TableName[ea] RETURN IF ( NOT ISBLANK ( EndDate ), VAR StartDate = EndDate - 2 * Period VAR DatesPeriod = CALENDAR ( StartDate, EndDate ) VAR DatesAndWDs = ADDCOLUMNS ( DatesPeriod, "@WorkingDay", VAR DayOfWeek = WEEKDAY ( [Date], 2 ) RETURN IF ( DayOfWeek IN { 6, 7 }, "No", "Yes" ) ) VAR WDsOnly = FILTER ( DatesAndWDs, [@WorkingDay] = "Yes" ) VAR TopNTable = TOPN ( Period + 1, WDsOnly, [Date], DESC ) RETURN MINX ( TopNTable, [Date] ) )Thanks! It works perfect!!