Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
xRTP
Helper V
Helper V

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!


CA Inventory.PNG

1 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 exampleMIN(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" ) )



Did I answer your question? Mark my post as a solution!


https://allure-analytics.com/
https://www.youtube.com/channel/UCndD_QZVNB_JWYLEmP6KrpA
https://www.linkedin.com/company/77757292/

Proud to be a Super User!




View solution in original post

6 REPLIES 6
ALLUREAN
Solution Sage
Solution Sage

Hi, @xRTP 

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

custom.png




Did I answer your question? Mark my post as a solution!


https://allure-analytics.com/
https://www.youtube.com/channel/UCndD_QZVNB_JWYLEmP6KrpA
https://www.linkedin.com/company/77757292/

Proud to be a Super User!




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

xRTP_0-1653768883612.png

 

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 exampleMIN(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" ) )



Did I answer your question? Mark my post as a solution!


https://allure-analytics.com/
https://www.youtube.com/channel/UCndD_QZVNB_JWYLEmP6KrpA
https://www.linkedin.com/company/77757292/

Proud to be a Super User!




Hello @ALLUREAN,

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

xRTP_0-1653774448979.png

 

 

 

Hi, @xRTP 

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

2022-05-30_20-54-04.png

Hope this will solve it!




Did I answer your question? Mark my post as a solution!


https://allure-analytics.com/
https://www.youtube.com/channel/UCndD_QZVNB_JWYLEmP6KrpA
https://www.linkedin.com/company/77757292/

Proud to be a Super User!




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?

xRTP_0-1654086138671.png

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.