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

Get 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

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
November Carousel

Fabric Community Update - November 2024

Find out what's new and trending in the Fabric Community.

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.

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! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

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