Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreGet certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now
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.
Solved! Go to Solution.
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 @Bergh
This one is tested and confirmed working both ways. If you want to add dates you use N if youi want to minus days you use -N
ea_plus/minus_9 =
VAR Period = -9
VAR Date1 = 'Date'[Date]
RETURN
IF (
NOT ISBLANK ( Date1 ),
VAR Date2 = Date1 + 2 * Period
VAR DatesPeriod =
CALENDAR ( MIN ( Date1, Date2 ), MAX ( Date1, Date2 ) )
VAR DatesAndWDs =
ADDCOLUMNS (
DatesPeriod,
"@WorkingDay",
VAR DayOfWeek =
WEEKDAY ( [Date], 2 )
RETURN
IF ( DayOfWeek IN { 6, 7 }, "No", "Yes" )
)
VAR WDsOnly =
FILTER ( DatesAndWDs, [@WorkingDay] = "Yes" )
RETURN
IF (
Date1 < Date2,
MAXX ( TOPN ( ABS ( Period ) + 1, WDsOnly, [Date], ASC ), [Date] ),
MINX ( TOPN ( ABS ( Period ) + 1, WDsOnly, [Date], DESC ), [Date] )
)
)
Yes, now it works thanks!!
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??
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] )
)
If I want to Period = -6 Can I do that??
@Bergh
Not tested but I guess this should work both ways
ea_minus_9 =
VAR Period = 9
VAR Date1 = TableName[ea]
RETURN
IF (
NOT ISBLANK ( EndDate ),
VAR Date2 = Date1 - 2 * Period
VAR DatesPeriod =
CALENDAR ( MAX ( Date1, Date2 ), MAX ( Date1, Date2 ) )
VAR DatesAndWDs =
ADDCOLUMNS (
DatesPeriod,
"@WorkingDay",
VAR DayOfWeek =
WEEKDAY ( [Date], 2 )
RETURN
IF ( DayOfWeek IN { 6, 7 }, "No", "Yes" )
)
VAR WDsOnly =
FILTER ( DatesAndWDs, [@WorkingDay] = "Yes" )
RETURN
IF (
Date1 > Date2,
MINX ( TOPN ( Period + 1, WDsOnly, [Date], DESC ), [Date] ),
MINX ( TOPN ( Period - 1, WDsOnly, [Date], ASC ), [Date] )
)
)
No nothing happens. It is the same date 2022-05-20 become 2022-05-20
Thanks! It works perfect!!
Hi @Bergh
Not sure if your talking about a date table. However if you dont have a weekday column you can create one or just store in a variable
Working Day =
VAR DayOfWeek = WEEKDAY( 'Date'[Date], 2)
RETURN
IF ( DayOfWeek In { 6, 7 }, "No", "Yes" )
You can then use this calculated column to filter your data
9 Days Ago =
MINX (
TOPN (
9,
FILTER ( 'Date', 'Date'[Date] < EARLIER ( 'Date'[Date] ) && 'Date'[Working Day] = "Yes" ),
'Date'[Date],
DESC
),
'Date'[Date]
)
Hi,
One way to do this is to add the following column in your calendar table:
I hope this post helps to solve your issue and if it does consider accepting it as a solution and giving the post a thumbs up!
My LinkedIn: https://www.linkedin.com/in/n%C3%A4ttiahov-00001/
Proud to be a Super User!
Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
User | Count |
---|---|
21 | |
20 | |
19 | |
13 | |
12 |
User | Count |
---|---|
42 | |
28 | |
23 | |
22 | |
22 |