Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

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...
Need Help
Show and Tell
Tips and Tricks
  • ALLUREAN's avatar
    ALLUREAN
    4 years ago

    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" ) )
ALLUREAN's avatar
ALLUREAN
Solution Sage
4 years ago

Hi, Anonymous 

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

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

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

     

    • ALLUREAN's avatar
      ALLUREAN
      Solution Sage
      4 years ago

      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's avatar
        Anonymous
        Not applicable
        4 years ago

        Hello ALLUREAN,

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