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.
Solved! Go to Solution.
Here is a quick solution. First thing you need is a Date Table that has a Day of the Week column with the day name included. Then you can use this measure:
Date Range Output = var startDate = FIRSTDATE('Date Table'[Date]) var endDate = LASTDATE('Date Table'[Date]) var dateLength = DATEDIFF(startDate, endDate, DAY) var weeksMOD = MOD(dateLength, 7) var weeksDIV = INT(DIVIDE(dateLength, 7)) var weekString = if (weeksMOD = 1, " week", " weeks") var lastWeekday = LOOKUPVALUE('Date Table'[Day of Week], 'Date Table'[Date], enddate) var WeekdayM1 = LOOKUPVALUE('Date Table'[Day of Week], 'Date Table'[Date], enddate - 1) var WeekdayM2 = LOOKUPVALUE('Date Table'[Day of Week], 'Date Table'[Date], enddate - 2) var WeekdayM3 = LOOKUPVALUE('Date Table'[Day of Week], 'Date Table'[Date], enddate - 3) var WeekdayM4 = LOOKUPVALUE('Date Table'[Day of Week], 'Date Table'[Date], enddate - 4) var WeekdayM5 = LOOKUPVALUE('Date Table'[Day of Week], 'Date Table'[Date], enddate - 5) RETURN weeksDIV & weekString & SWITCH( weeksMOD, 1, " + " & lastWeekday, 2, " + " & WeekdayM1 & " + " & lastWeekday, 3, " + " & WeekdayM2 & " + " & WeekdayM1 & " + " & lastWeekday, 4, " + " & WeekdayM3 & " + " & WeekdayM2 & " + " & WeekdayM1 & " + " & lastWeekday, 5, " + " & WeekdayM4 & " + " & WeekdayM3 & " + " & WeekdayM2 & " + " & WeekdayM1 & " + " & lastWeekday, 6, " + " & WeekdayM5 & " + " & WeekdayM4 & " + " & WeekdayM3 & " + " & WeekdayM2 & " + " & WeekdayM1 & " + " & lastWeekday, "" )
Here is a quick solution. First thing you need is a Date Table that has a Day of the Week column with the day name included. Then you can use this measure:
Date Range Output = var startDate = FIRSTDATE('Date Table'[Date]) var endDate = LASTDATE('Date Table'[Date]) var dateLength = DATEDIFF(startDate, endDate, DAY) var weeksMOD = MOD(dateLength, 7) var weeksDIV = INT(DIVIDE(dateLength, 7)) var weekString = if (weeksMOD = 1, " week", " weeks") var lastWeekday = LOOKUPVALUE('Date Table'[Day of Week], 'Date Table'[Date], enddate) var WeekdayM1 = LOOKUPVALUE('Date Table'[Day of Week], 'Date Table'[Date], enddate - 1) var WeekdayM2 = LOOKUPVALUE('Date Table'[Day of Week], 'Date Table'[Date], enddate - 2) var WeekdayM3 = LOOKUPVALUE('Date Table'[Day of Week], 'Date Table'[Date], enddate - 3) var WeekdayM4 = LOOKUPVALUE('Date Table'[Day of Week], 'Date Table'[Date], enddate - 4) var WeekdayM5 = LOOKUPVALUE('Date Table'[Day of Week], 'Date Table'[Date], enddate - 5) RETURN weeksDIV & weekString & SWITCH( weeksMOD, 1, " + " & lastWeekday, 2, " + " & WeekdayM1 & " + " & lastWeekday, 3, " + " & WeekdayM2 & " + " & WeekdayM1 & " + " & lastWeekday, 4, " + " & WeekdayM3 & " + " & WeekdayM2 & " + " & WeekdayM1 & " + " & lastWeekday, 5, " + " & WeekdayM4 & " + " & WeekdayM3 & " + " & WeekdayM2 & " + " & WeekdayM1 & " + " & lastWeekday, 6, " + " & WeekdayM5 & " + " & WeekdayM4 & " + " & WeekdayM3 & " + " & WeekdayM2 & " + " & WeekdayM1 & " + " & lastWeekday, "" )
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 |
---|---|
98 | |
98 | |
80 | |
76 | |
66 |
User | Count |
---|---|
135 | |
109 | |
104 | |
83 | |
73 |