Forum Discussion
Custom Graph
Hello, is it possible to create a graph like this in power BI? as you can see the dates are group by week, i can only do is slicer is it possible to be like that? and it is comboed with bar line area graphs. Im open to any opinion and suggestions to create a new visuals for this kind of graph thank you!
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" ) )
6 Replies
- ALLUREAN
Solution Sage
Hi, Anonymous
You can import custom visual Line and Stacked Column Chart with Table from AppSource.
- ALLUREAN
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" ) )