Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Dear all,
I would like to requst your assistance on how to add a new column that counts the number of mondays (or any other day) between a date column with a starting date and a date column with an end date?
Both DATE_START and DATE_END should be included in the calculation, but if DATE_START and DATE_END are the same date value it should should only copunt as one "monday".
I have the result, I would like in excel. I would however much prefer to have it in my Power Bi-model...
I hope you can help!
All the best,
Jacob
Solved! Go to Solution.
@JK87 You can do something like this:
Measure =
VAR __Table =
ADDCOLUMNS(
CALENDAR( [DATE_START], [DATE_END] ),
"__WeekDay", WEEKDAY( [Date], 2 )
)
VAR __Result = COUNTROWS( FILTER( __Table, [__WeekDay] = 1) ) // this is for Monday
RETURN
__Result
Please help me bellow
thanks ahead
Original data
Work Date ID Qty amount
2023-02-28 FS-002 1 15
2023-02-28 FS-002 2 22
2023-02-28 FS-003 3 31
2023-02-28 FS-003 1 34
2023-02-28 FS-003 2 15
2023-02-28 FS-004 3 15
2023-02-28 FS-005 1 15
2023-02-28 FS-005 2 15
2023-02-28 FS-005 3 15
2023-02-28 FS-005 1 15
2023-02-28 FS-005 2 15
2023-02-28 FS-005 3 15
2023-02-28 FS-005 1 15
2023-03-01 FS-003 2 15
2023-03-01 FS-002 3 15
2023-03-01 FS-002 1 15
2023-03-01 FS-002 2 15
2023-03-01 FS-002 3 15
2023-03-01 FS-002 1 15
Expecting Result
Work Date ID Count Qty sum amount
2023-02-28 4 13 237
2023-03-01 2 6 90
@JK87 You can do something like this:
Measure =
VAR __Table =
ADDCOLUMNS(
CALENDAR( [DATE_START], [DATE_END] ),
"__WeekDay", WEEKDAY( [Date], 2 )
)
VAR __Result = COUNTROWS( FILTER( __Table, [__WeekDay] = 1) ) // this is for Monday
RETURN
__Result
User | Count |
---|---|
102 | |
92 | |
85 | |
78 | |
71 |
User | Count |
---|---|
113 | |
104 | |
101 | |
73 | |
65 |