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 graphs. Im open to any opinion and suggestions to create a new visuals for this kind of graph thank you!


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

6 Replies

  • 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" ) )