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.
Hi,
I made a Date table as following.
When select a YearMonth such as 2020/05, the visual will show its YearWeeknum.
However, I also want it show these YearWeeknum belongs to which date.
So I want to write a new column like the following:
For example, when select YearMonth 202005, it should be 2020/5/1-2020/5/3 (which is YearWeeknum 202018 in the previous bar chart.), 2020/5/4-2020/5/10, 2020/5/11-2020/5/17........
Anyone know how to write this new column in power bi?
Thanks a lot for your time!
Solved! Go to Solution.
Hi, @YunJ
I'd like to suggest you create a calculated column as below. The pbix file is attached in the end.
DateDuration =
var _min =
CALCULATE(
MIN('Date'[Date]),
FILTER(
ALL('Date'),
'Date'[YearWeeknum]=EARLIER('Date'[YearWeeknum])&&
'Date'[Month]=EARLIER('Date'[Month])
)
)
var _max =
CALCULATE(
MAX('Date'[Date]),
FILTER(
ALL('Date'),
'Date'[YearWeeknum]=EARLIER('Date'[YearWeeknum])&&
'Date'[Month]=EARLIER('Date'[Month])
)
)
return
_min&"-"&_max
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, @YunJ
I'd like to suggest you create a calculated column as below. The pbix file is attached in the end.
DateDuration =
var _min =
CALCULATE(
MIN('Date'[Date]),
FILTER(
ALL('Date'),
'Date'[YearWeeknum]=EARLIER('Date'[YearWeeknum])&&
'Date'[Month]=EARLIER('Date'[Month])
)
)
var _max =
CALCULATE(
MAX('Date'[Date]),
FILTER(
ALL('Date'),
'Date'[YearWeeknum]=EARLIER('Date'[YearWeeknum])&&
'Date'[Month]=EARLIER('Date'[Month])
)
)
return
_min&"-"&_max
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, @YunJ
I'd like to suggest you modify the calculated table as below.
Date =
ADDCOLUMNS(
ADDCOLUMNS(
CALENDAR(DATE(2020,1,1),DATE(2020,12,31)),
"Year",YEAR([Date]),
"Quarter","Q"&FORMAT([Date],"Q"),
"Month",FORMAT([Date],"MM"),
"Day",FORMAT([Date],"DD"),
"YearQuarter",FORMAT([Date],"YYYY")&"Q"&FORMAT([Date],"Q"),
"YearMonth",FORMAT([Date],"YYYY/MM"),
"Weekday",WEEKDAY([Date],2),
"Weeknum",WEEKNUM([Date],2)
),
"YearWeeknum",[Year]*100+[Weeknum],
"YearMonthWeeknum",[Year]*100+[Month]*10+[Weeknum]
)
Then you may make 'DateDuration' column selected, click 'Sort by column' in 'Column tools', sort by 'YearMonthWeeknum'.
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.
@YunJ , not very clear to me. Do need week start and end date
Week Start date = 'Date'[Date]+-1*WEEKDAY('Date'[Date],2)+1 //Monday start
Week End date = 'Date'[Date]+ 7-1*WEEKDAY('Date'[Date],2)
Week Start date = 'Date'[Date]+-1*WEEKDAY('Date'[Date],2)+1 //Sunday Start
Week End date = 'Date'[Date]+ 7-1*WEEKDAY('Date'[Date],2)
Week name = [Week Start date] & " to "& [Week End date]
Hi @YunJ ,
Please use the below one:
Measure = CALCULATE(MIN('Table'[Date]),ALL('Table'[Year],'Table'[Quarter],'Table'[Month],'Table'[Day],'Table'[YearQuarter],'Table'[YearMonth],'Table'[Weekday],'Table'[Weeknum],'Table'[Date])) & "-" & CALCULATE(MAX('Table'[Date]),ALL('Table'[Year],'Table'[Quarter],'Table'[Month],'Table'[Day],'Table'[YearQuarter],'Table'[YearMonth],'Table'[Weekday],'Table'[Weeknum],'Table'[Date]))
Aiolos Zhao
Proud to be a Super User!
Hi @YunJ ,
Create a Column
Thanks for your help.
For example for 2020/5/3, because it belongs to May, so it should be 5/1/2020-5/3/2020. I want it return the start/end start for the month itself.
You need a if-else for this:
Measure = IF(CALCULATE(MIN('Table'[Date]),ALLEXCEPT('Table','Table'[YearWeeknum])) < STARTOFMONTH('Table'[Date]),STARTOFMONTH('Table'[Date]),CALCULATE(MIN('Table'[Date]),ALLEXCEPT('Table','Table'[YearWeeknum])))
& "-" &
IF(CALCULATE(MAX('Table'[Date]),ALLEXCEPT('Table','Table'[YearWeeknum])) > ENDOFMONTH('Table'[Date]),ENDOFMONTH('Table'[Date]),CALCULATE(MAX('Table'[Date]),ALLEXCEPT('Table','Table'[YearWeeknum])))
Aiolos Zhao
Proud to be a Super User!
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 |
---|---|
113 | |
99 | |
75 | |
73 | |
49 |
User | Count |
---|---|
145 | |
109 | |
109 | |
90 | |
64 |