Return cycle number for every first Wednesday and third Wednesday of the month

[Kasonga2018]

Hi all,

I need help please, I have a date table below, and I want to add a new column that will return an incremental cycle number for every first Wednesday and third Wednesday of the month. Basically for Wed, 03 Jun'20, it should return 1, for Wed, 17 Jun'20, it should return 2, then for Wed, 01 Jul'20, it should return 3 and Wed, 15 Jul'20, it should return 4 and so on. Does anyone know how I can achieve this with DAX?

[Greg_Deckler · Accepted solution]

@Kasonga2018 You could do something like this, 2 columns:

1st or 3rd Wednesday = 
    VAR __Date = [Date]
    VAR __Year = YEAR([Date])
    VAR __Month = MONTH([Date])
    VAR __EOMonth = EOMONTH([Date],0)
    VAR __Calendar = 
            ADDCOLUMNS(
                CALENDAR(DATE(__Year,__Month,1),__EOMonth),
                "__Weekday",WEEKDAY([Date],2)
            )
    VAR __Calendar1 = 
        ADDCOLUMNS(
            __Calendar,
            "__WedNum",COUNTROWS(FILTER(__Calendar,[__Weekday] = 3 && [Date]<=EARLIER([Date])))
        )
    VAR __1stWed = MAXX(FILTER(__Calendar1,[__Weekday] = 3 && [__WedNum] = 1),[Date])
    VAR __3rdWed = MAXX(FILTER(__Calendar1,[__Weekday] = 3 && [__WedNum] = 3),[Date])
RETURN
    IF(__Date = __1stWed || __Date = __3rdWed,1,BLANK())





Sequential 1st or 3rd Wednesday = 
    IF([1st or 3rd Wednesday] = 1,COUNTROWS(FILTER('Dates',[1st or 3rd Wednesday] = 1 && [Date] <= EARLIER([Date]))),BLANK())