cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
xRTP
Helper III
Helper III

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
Super User
Super User

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
Power BI Show Episode 10 Recap

The Power BI Community Show

Watch the playback when Amit Chandak, a Power BI Super User, demos how to use Field Parameters to make reports more dynamic.

Health and Life Sciences Power BI User Group

Health and Life Sciences Power BI User Group

Power BI specialists at Microsoft have created a community user group where customers in the provider, payor, pharma, health solutions, and life science industries can collaborate.

Ignite 2022

What's Next at Microsoft Ignite 2022

Explore the latest innovations, learn from product experts and partners, level up your skillset, and create connections from around the world.

Top Solution Authors