The ultimate Fabric, Power BI, SQL, and AI community-led learning event. Save €200 with code FABCOMM.
Get registeredCompete to become Power BI Data Viz World Champion! First round ends August 18th. Get started.
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?
Solved! Go to 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())
@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())
User | Count |
---|---|
25 | |
10 | |
8 | |
7 | |
6 |
User | Count |
---|---|
32 | |
12 | |
10 | |
10 | |
9 |