Forum Discussion

Bergh's avatar
Bergh
Helper II
4 years ago
Solved

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.

Date
Weekday
  • tamerj1's avatar
    tamerj1
    4 years ago
    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

  • ValtteriN's avatar
    ValtteriN
    Community Champion
    4 years ago

    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/

  • tamerj1's avatar
    tamerj1
    Community Champion
    4 years ago

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

     

  • Bergh's avatar
    Bergh
    Helper II
    4 years ago

    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

    • tamerj1's avatar
      tamerj1
      Community Champion
      4 years ago

      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] )
      • Bergh's avatar
        Bergh
        Helper II
        4 years ago

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

  • tamerj1's avatar
    tamerj1
    Community Champion
    4 years ago

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