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

If I want to Period = -6 Can I do that??

tamerj1's avatar
tamerj1
Community Champion
4 years ago

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

    No nothing happens. It is the same date 2022-05-20 become 2022-05-20