Custom Graph

[Anonymous]

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!

[ALLUREAN]

Hi, @Anonymous 

You can import custom visual Line and Stacked Column Chart with Table from AppSource.

[Anonymous]

This is great! follow up question hehe, how to make this in weeks? like May 23-27?

[ALLUREAN · Accepted solution]

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 here

TODAY() ), --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 here

TODAY() ), --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" ) )

[Anonymous]

Hello @ALLUREAN,

Hey it works! one last thing, how to sort it from 1-6? 🙂 

[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!

[Anonymous]

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?