Visualizing Case Trends with Subscription Start and End Dates for Accounts

[Ricky97]

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-subscriptio...

[Anonymous]

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

[shafiz_p · Accepted solution]

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

[danextian]

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]

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]

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