Forum Discussion

Ricky97's avatar
Ricky97
Regular Visitor
1 year ago
Solved

Visualizing Case Trends with Subscription Start and End Dates for Accounts

I need to track the total number of cases raised by an account, where each account will raise multiple subscription requests, each with a start and end date. I'd like to create a plot that shows the total number of cases raised by the account over time, with subscription start and end dates marked as lines. This will help identify trends in case counts and allow for comparisons during the subscription periods.

I have attached a sample Excel file where each case is tagged to a subscription name based on the case creation date (if the creation date falls between the subscription’s start and end dates)

 

Attaching the prototype here :

 

I have attached the files in tableau community since I am unable to attach it her. Your help is much appreciated.

https://community.tableau.com/s/question/0D5cw000006RpqzCAC/visualizing-case-trends-with-subscription-start-and-end-dates-for-accounts

General Comment
Need Help
Show and Tell
Tips and Tricks
Tutorial Requests
  • shafiz_p's avatar
    shafiz_p
    1 year ago

    Hi Ricky97 
    In power bi line chart you would not be able to create a x axis constant line for categorical or text label. If you have number in X axis, then you can create constant line to identify subscription start and end date. But in your case it is a categorical.

    You need to use custome visual which provide such functionality.

    However, you could get similar artifact using Line and Stacked column chart. See Some of the examples:

    Here is the data model:

     

    Created few extra column in Subscription table, See code below:

    MonthYearSubscriptionTable = FORMAT('Subscription'[START DATE], "MMM-YYYY")

MonthYearEndDate = FORMAT('Subscription'[END DATE], "MMM-YYYY")

    Image:

     

     

    To create a column for Subscription Start Date, Use this measure:

    CheckMonthYearMatch = 
IF(
    CALCULATE(
        COUNTROWS('Subscription'),
        'Subscription'[MonthYearSubscriptionTable] = SELECTEDVALUE('Calendar'[Month-Year])
    ) > 0,
    7,
    BLANK()
)

     To create a column for Subscription End Date, Use this measure:

    CheckMonthYearMatchEnd = 
IF(
    CALCULATE(
        COUNTROWS('Subscription'),
        'Subscription'[MonthYearEndDate] = SELECTEDVALUE('Calendar'[Month-Year])
    ) > 0,
    7, // You could use measure to get the max count within all month, to set the hight of the column
    BLANK()
)

     

    For custom data label for columns, Create below measure for Subscription Start Date:

    CheckMonthYearMatchStartDate = 
IF(
    CALCULATE(
        COUNTROWS('Subscription'),
        'Subscription'[MonthYearSubscriptionTable] = SELECTEDVALUE('Calendar'[Month-Year])
    ) > 0,
    "Start Date : " & FORMAT(MINX(
        FILTER(
            'Subscription',
            'Subscription'[MonthYearSubscriptionTable] = SELECTEDVALUE('Calendar'[Month-Year])
        ),
        'Subscription'[START DATE]
    ), "MM-DD-YY") & " " &
    MINX(
        FILTER(
            'Subscription',
            'Subscription'[MonthYearSubscriptionTable] = SELECTEDVALUE('Calendar'[Month-Year])
        ),
        'Subscription'[CR Name]
    ),
    BLANK()
)

     

    For Subscription End Date:

    CheckMonthYearMatchEndDate = 
IF(
    CALCULATE(
        COUNTROWS('Subscription'),
        'Subscription'[MonthYearEndDate] = SELECTEDVALUE('Calendar'[Month-Year])
    ) > 0,
    "End Date : " & FORMAT(MINX(
        FILTER(
            'Subscription',
            'Subscription'[MonthYearEndDate] = SELECTEDVALUE('Calendar'[Month-Year])
        ),
        'Subscription'[END DATE]
    ), "MM-DD-YY") & " " & 
    MINX(
        FILTER(
            'Subscription',
            'Subscription'[MonthYearEndDate] = SELECTEDVALUE('Calendar'[Month-Year])
        ),
        'Subscription'[CR Name]
    ),
    BLANK()
)

     

     

    Place measure to create Visual column in the visual Column-Y Axis, and CountCase measure in the Line Y-Axis:

    Update data labels with the corresponding measure, see image :

     

    PBIX : 

     

     

    Hope this helps!!

    If this solved your problem, please accept it as a solution and a kudos!!

    Best Regards,
    Shahariar Hafiz

5 Replies

  • danextian's avatar
    danextian
    Super User
    1 year ago

    Hi Ricky97 

    Try the following measure

     

    Case Count = 
SUMX (
    ADDCOLUMNS (
        SUMMARIZE (
            SubsciptionDate,
            SubsciptionDate[ACCOUNT],
            SubsciptionDate[CR Name],
            SubsciptionDate[START DATE],
            SubsciptionDate[END DATE]
        ),
        "@Count",
            COUNTROWS (
                FILTER (
                    VALUES ( 'Case'[Case Create] ),
                    'Case'[Case Create] >= [START DATE]
                        && 'Case'[Case Create] <= [END DATE]
                )
            )
    ),
    [@Count]
)

    Please see attached pbix for the details.

    • Ricky97's avatar
      Ricky97
      Regular Visitor
      1 year ago

      Hi danextian , Thank you for formatting it into a table. However, my requirement is slightly different. Could you guide me on how to create a line chart with overlapping subscription start and end dates as refernece lines? Attached the image for reference.

       

      • danextian's avatar
        danextian
        Super User
        1 year ago

        You will need a custom viz for that. I don't  think there's one that exists.

  • shafiz_p's avatar
    shafiz_p
    Super User
    1 year ago

    Hi Ricky97 
    In power bi line chart you would not be able to create a x axis constant line for categorical or text label. If you have number in X axis, then you can create constant line to identify subscription start and end date. But in your case it is a categorical.

    You need to use custome visual which provide such functionality.

    However, you could get similar artifact using Line and Stacked column chart. See Some of the examples:

    Here is the data model:

     

    Created few extra column in Subscription table, See code below:

    MonthYearSubscriptionTable = FORMAT('Subscription'[START DATE], "MMM-YYYY")

MonthYearEndDate = FORMAT('Subscription'[END DATE], "MMM-YYYY")

    Image:

     

     

    To create a column for Subscription Start Date, Use this measure:

    CheckMonthYearMatch = 
IF(
    CALCULATE(
        COUNTROWS('Subscription'),
        'Subscription'[MonthYearSubscriptionTable] = SELECTEDVALUE('Calendar'[Month-Year])
    ) > 0,
    7,
    BLANK()
)

     To create a column for Subscription End Date, Use this measure:

    CheckMonthYearMatchEnd = 
IF(
    CALCULATE(
        COUNTROWS('Subscription'),
        'Subscription'[MonthYearEndDate] = SELECTEDVALUE('Calendar'[Month-Year])
    ) > 0,
    7, // You could use measure to get the max count within all month, to set the hight of the column
    BLANK()
)

     

    For custom data label for columns, Create below measure for Subscription Start Date:

    CheckMonthYearMatchStartDate = 
IF(
    CALCULATE(
        COUNTROWS('Subscription'),
        'Subscription'[MonthYearSubscriptionTable] = SELECTEDVALUE('Calendar'[Month-Year])
    ) > 0,
    "Start Date : " & FORMAT(MINX(
        FILTER(
            'Subscription',
            'Subscription'[MonthYearSubscriptionTable] = SELECTEDVALUE('Calendar'[Month-Year])
        ),
        'Subscription'[START DATE]
    ), "MM-DD-YY") & " " &
    MINX(
        FILTER(
            'Subscription',
            'Subscription'[MonthYearSubscriptionTable] = SELECTEDVALUE('Calendar'[Month-Year])
        ),
        'Subscription'[CR Name]
    ),
    BLANK()
)

     

    For Subscription End Date:

    CheckMonthYearMatchEndDate = 
IF(
    CALCULATE(
        COUNTROWS('Subscription'),
        'Subscription'[MonthYearEndDate] = SELECTEDVALUE('Calendar'[Month-Year])
    ) > 0,
    "End Date : " & FORMAT(MINX(
        FILTER(
            'Subscription',
            'Subscription'[MonthYearEndDate] = SELECTEDVALUE('Calendar'[Month-Year])
        ),
        'Subscription'[END DATE]
    ), "MM-DD-YY") & " " & 
    MINX(
        FILTER(
            'Subscription',
            'Subscription'[MonthYearEndDate] = SELECTEDVALUE('Calendar'[Month-Year])
        ),
        'Subscription'[CR Name]
    ),
    BLANK()
)

     

     

    Place measure to create Visual column in the visual Column-Y Axis, and CountCase measure in the Line Y-Axis:

    Update data labels with the corresponding measure, see image :

     

    PBIX : 

     

     

    Hope this helps!!

    If this solved your problem, please accept it as a solution and a kudos!!

    Best Regards,
    Shahariar Hafiz

  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    Hi Ricky97 ,

     

    Did shafiz_p  danextian reply solve your problem? If so, please mark it as the correct solution, and point out if the problem persists.

     

    Best regards,

    Adamk Kong