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

Be one of the first to start using Fabric Databases. View on-demand sessions with database experts and the Microsoft product team to learn just how easy it is to get started. Watch now

Reply
Bergh
Helper II
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

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

View solution in original post

12 REPLIES 12
tamerj1
Super User
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] )
            )
    )

 

Yes, now it works thanks!!

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

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!!

 

tamerj1
Super User
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]
)

 

ValtteriN
Super User
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:

ValtteriN_0-1653029744252.png


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
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!

Dec Fabric Community Survey

We want your feedback!

Your insights matter. That’s why we created a quick survey to learn about your experience finding answers to technical questions.

ArunFabCon

Microsoft Fabric Community Conference 2025

Arun Ulag shares exciting details about the Microsoft Fabric Conference 2025, which will be held in Las Vegas, NV.

December 2024

A Year in Review - December 2024

Find out what content was popular in the Fabric community during 2024.