Forum Discussion
Custom Graph
If you don't have Date table, you can create in order to have weekly calculation. Please check my article on how to create a Date table https://allure-analytics.com/index.php/2022/05/14/standard-calendar-date-table-in-power-bi/
and you can use for example ULTIMATE DYNAMIC DAX CALENDAR + FISCAL PERIODS
where there are calculations for Month name (May), Start of Week (start date) and End of Week (end date) and most probably you need to create a combination of Month name and start & end week date.
Here is the code you can paste as create new table. MonthWeekPeriod will show like May 23-29 (+Weekends)
Also, you need to change your start and end date based on your sales table for example: MIN(fctSales[DateKey]) to MIN(YourTable[Date] and MAX respectively
Date =ADDCOLUMNS(CALENDAR(MIN(fctSales[DateKey]), --Set Start date hereTODAY() ), --Set End date here"DateAsInteger", FORMAT ( [Date], "YYYYMMDD" ),"Year", YEAR ( [Date] ),"Monthnumber", FORMAT ( [Date], "MM" ),"YearMonthnumber", FORMAT ( [Date], "YYYY/MM" ),"YearMonthShort", FORMAT ( [Date], "YYYY/mmm" ),"MonthNameShort", FORMAT ( [Date], "mmm" ),"MonthNameLong", FORMAT ( [Date], "mmmm" ),"Week of Year", WEEKNUM([Date],2),"DayOfWeekNumber", WEEKDAY ( [Date],2 ), --by default start Sun-Sat, 2-Mon-Sun"DayOfWeek", FORMAT ( [Date], "dddd" ),"DayOfWeekShort", FORMAT ( [Date], "ddd" ),"Quarter", "Q" & FORMAT ( [Date], "Q" ),"Start of Week", [Date]-WEEKDAY([Date],2)+1,"End of Week", [Date]+7-WEEKDAY([Date],2),"MonthWeekPeriod", FORMAT ( [Date], "mmm" ) &" "& LEFT([Date]-WEEKDAY([Date],2)+1,2) & "-" & LEFT([Date]+7-WEEKDAY([Date],2),2),"YearQuarter", FORMAT ( [Date], "YYYY" ) & "/Q" & FORMAT ( [Date], "Q" ) )NOTE: In your case the week is 23-27 (no weekends) so the code will be:Date =ADDCOLUMNS(CALENDAR(MIN(fctSales[DateKey]), --Set Start date hereTODAY() ), --Set End date here"DateAsInteger", FORMAT ( [Date], "YYYYMMDD" ),"Year", YEAR ( [Date] ),"Monthnumber", FORMAT ( [Date], "MM" ),"YearMonthnumber", FORMAT ( [Date], "YYYY/MM" ),"YearMonthShort", FORMAT ( [Date], "YYYY/mmm" ),"MonthNameShort", FORMAT ( [Date], "mmm" ),"MonthNameLong", FORMAT ( [Date], "mmmm" ),"Week of Year", WEEKNUM([Date],2),"DayOfWeekNumber", WEEKDAY ( [Date],2 ), --by default start Sun-Sat, 2-Mon-Sun"DayOfWeek", FORMAT ( [Date], "dddd" ),"DayOfWeekShort", FORMAT ( [Date], "ddd" ),"Quarter", "Q" & FORMAT ( [Date], "Q" ),"Start of Week", [Date]-WEEKDAY([Date],2)+1,"End of Week", [Date]+5-WEEKDAY([Date],2),"MonthWeekPeriod", FORMAT ( [Date], "mmm" ) &" "& LEFT([Date]-WEEKDAY([Date],2)+1,2) & "-" & LEFT([Date]+5-WEEKDAY([Date],2),2),"YearQuarter", FORMAT ( [Date], "YYYY" ) & "/Q" & FORMAT ( [Date], "Q" ) )
This is great! follow up question hehe, how to make this in weeks? like May 23-27?
Solution SageIf you don't have Date table, you can create in order to have weekly calculation. Please check my article on how to create a Date table https://allure-analytics.com/index.php/2022/05/14/standard-calendar-date-table-in-power-bi/
and you can use for example ULTIMATE DYNAMIC DAX CALENDAR + FISCAL PERIODS
where there are calculations for Month name (May), Start of Week (start date) and End of Week (end date) and most probably you need to create a combination of Month name and start & end week date.
Here is the code you can paste as create new table. MonthWeekPeriod will show like May 23-29 (+Weekends)
Also, you need to change your start and end date based on your sales table for example: MIN(fctSales[DateKey]) to MIN(YourTable[Date] and MAX respectively
- ALLUREAN
Hi, Anonymous
Typically, in Date table when you click on Month name (Jan, Feb) and go to Sort by column you can select month number (1-12). Therefore months will be in natural sequence Jan-Dec, not alphabetically like Apr, Aug...etc.
Also you can try to click on MonthWeekPeriod and sort it by month number (1-12)
In your case if not working just try sorting in the custom visual itself where most probably this is not sorted by your period. On the screenshot sort is by MonthWeekPeriod, not Week of Year in my case
Hope this will solve it!
Hello already sort this but thanks anyway, got another question. I followed your code above then this should show like May 20-24 but for me it shows like this. What am i missing?