Skip to main content
cancel
Showing results for
Search instead for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Helper II

## 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.

1 ACCEPTED SOLUTION
Super User
``````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] )
)``````
12 REPLIES 12
Super User

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] )
)
)``````

Helper II

Yes, now it works thanks!!

Helper II

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

Super User

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] )``````
Helper II

I had some blank cells, then iI get a error. How do I do to fix this??

Super User
``````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] )
)``````
Helper II

If I want to Period = -6 Can I do that??

Super User

@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] )
)
)``````
Helper II

No nothing happens. It is the same date 2022-05-20 become 2022-05-20

Helper II

Thanks! It works perfect!!

Super User

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]
)``````

Super User

Hi,

One way to do this is to add the following column in your calendar table:

Working days before today = IF(and(and(WEEKDAY('Calendar'[Date],2)<>6,WEEKDAY('Calendar'[Date],2)<>7),[date]<=TODAY()),1,0)

Then simply use these as a filter:

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/

Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

## Helpful resources

Announcements

#### Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

Top Solution Authors
Top Kudoed Authors