cancel
Showing results for 
Search instead for 
Did you mean: 

Fabric is Generally Available. Browse Fabric Presentations. Work towards your Fabric certification with the Cloud Skills Challenge.

Reply
Bergh
Helper I
Helper I

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 I
Helper I

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
PBI November 2023 Update Carousel

Power BI Monthly Update - November 2023

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

Community News

Fabric Community News unified experience

Read the latest Fabric Community announcements, including updates on Power BI, Synapse, Data Factory and Data Activator.

Power BI Fabric Summit Carousel

The largest Power BI and Fabric virtual conference

130+ sessions, 130+ speakers, Product managers, MVPs, and experts. All about Power BI and Fabric. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors