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.
I want to have a filter on my reports with, the number of a week, but with the starting day of the corresponding week, for example "Week 1 (starting January 1)", I have the date table with the columns date (mm/dd/yyy format) and the number of the week (with weeknum function), is there another DAX command to do what I want?
Solved! Go to Solution.
Hi, @Julio9704
Based on your description, i created data to reproduce your scenario. The pbix file is attached in the end.
Calendar(a calculated table):
Calendar =
ADDCOLUMNS(
CALENDAR(
DATE(2021,1,1),
DATE(2021,12,31)
),
"YearWeek",
YEAR([Date])*100+WEEKNUM([Date])
)
You may create a calculated column as below.
StartOfWeek =
CALCULATE(
MIN('Calendar'[Date]),
FILTER(
'Calendar',
[YearWeek]=EARLIER('Calendar'[YearWeek])
)
)
Result:
Best Regards
Allan
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi, @Julio9704
Based on your description, i created data to reproduce your scenario. The pbix file is attached in the end.
Calendar(a calculated table):
Calendar =
ADDCOLUMNS(
CALENDAR(
DATE(2021,1,1),
DATE(2021,12,31)
),
"YearWeek",
YEAR([Date])*100+WEEKNUM([Date])
)
You may create a calculated column as below.
StartOfWeek =
CALCULATE(
MIN('Calendar'[Date]),
FILTER(
'Calendar',
[YearWeek]=EARLIER('Calendar'[YearWeek])
)
)
Result:
Best Regards
Allan
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
@Julio9704 , Not very cleat, if you date these can help . If not refer my wow blog. Last question convert yyyy ww into date
Week Start date = 'Date'[Date]+-1*WEEKDAY('Date'[Date],2)+1
Week End date = 'Date'[Date]+ 7-1*WEEKDAY('Date'[Date],2)
Week Number = WEEKNUM([Date],2)
Week = if('Date'[Week Number]<10,'Date'[Year]*10 & 'Date'[Week Number],'Date'[Year]&'Date'[Week Number])
Week Rank = RANKX(all('Date'),'Date'[Week Start date],,ASC,Dense)
Week name = [Week Start date] & " to "& [Week End date]
Weekday = WEEKDAY([Date],2)
WeekDay Name = FORMAT([Date],"ddd")
https://medium.com/@amitchandak.1978/power-bi-wtd-questions-time-intelligence-4-5-98c30fab69d3
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 |
---|---|
97 | |
96 | |
81 | |
74 | |
66 |
User | Count |
---|---|
129 | |
106 | |
106 | |
86 | |
72 |