Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
106 | |
97 | |
75 | |
63 | |
62 |
User | Count |
---|---|
135 | |
105 | |
104 | |
80 | |
65 |