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

 

  • Bergh's avatar
    Bergh
    Helper II
    4 years ago

    Yes, now it works thanks!!