Create a graph showing a Field evolution over time

[Anonymous]

I need to create a trend graph using data from a SharePoint list. 

The SharePoint data is for tickets that are 'Raised', 'In Progress', and 'Closed'. 'Status' is therefore a field that evolves over time.  I want to show how the count of open items is progressing. Problem is that if I create a 'Stacked area chart' visual and add 'status' as a 'Value' it just counts how many there are and shows as a single point:

As a workaround, I've manually exported the list to Excel and created a new table with the data break down required. 

How do I break down the 'status' data in the original SharePoint list to give me 'Σ IN PROGRESS' AND 'Σ RAISED', instead of just 'Count of Status'?

[Anonymous]

Hi @Anonymous ,

Option 1:

In your first example, could you try to add Status in the Legend as well as in Values?

Option 2:

An other option could be to create a DAX measure:

Number of open projects =
CALCULATE(
    COUNTROWS( Projects ),
    Projects[Status] = "Open"
)

You could then create similar measures for the other project statuses.

Then you add the different measures in the Value field of your visual.

I hope it works and help. Does it?

Have a nice day,

Thomas

[Anonymous]

Thanks for the quick reply @Anonymous 

Adding Status in the Legend as well as in Values works. The data is broken down into the individual categories of Status. However, it still only does this for a point in time, i.e. now. To show the evolution over time, PowerBI would have to retrieve historical data. Is this possible?

On my second screenshot, this was possible because I have been manually downloading the data weekly (Excel export).

[Anonymous · Accepted solution]

Hello

I'm beginning to better understand your scenarios: it's the Event in Progress pattern. You can do this with the original dataset (not the one you modified in Excel), without having to retrieve the weekly history.

I have worked on a solution with its original dataset and can find here the .pbix file.

I've used some dummy data:

6 tickets in total, 3 months in total, 2 tickets are collected each month.

At the end 2 are closed and 4 are still open

And I get the next visual (you could use a different one if you want)

Consideration of history

Do you need all the files for the history? I don't think you need it, as it has the High Date and Closed Date columns in the dataset that provide us with history.

Here we can:

• calculate for a specific period (one day, one month, or one year) the number of tickets collected, opened or closed during the period.
  These measures will help us check if our next measure returns correct values

• calculate the number of tickets currently collected or in progress (i.e. all tickets that are not yet closed)

Please note that because you do not have a date ticket in progress, we may over time calculate the number of tickets currently collected or in progress, but we cannot calculate over time the number of tickets currently in progress only

The main steps are:

1. Add a date table and connect it with your main table

2. Create the measure that returns the number of tickets raised/closed during the period

3. Create the final measure Currently raised or current entries to see evolution over time

Step 1: Create a date/calendar table

I used a standard DAX code:

Calendar = 
ADDCOLUMNS(
    CALENDAR(
        MIN(
            MIN( DataTickets[Date closed] ),
            MIN( DataTickets[Date raised] )
        ),
        MAX(
            MAX( DataTickets[Date closed] ),
            MAX( DataTickets[Date raised] )
        )
    ),
    "Month Number", MONTH([Date]),
    "Month", FORMAT([Date], "mmm"),
    "Year", YEAR([Date])
)

Next, you must connect the Calendar table to the main table:

• Calendar[Date] to MainTable[Date raised] as an active relationship
• Calendar[Date] to MainTable[Closed Date] as an inactive relationship

You should get the following:

Step 2: Create the measure to calculate what happened during a specific period:

Elevated entries in the period:we can take advantage of the active relationship and just write this

Raised tickets in period = COUNTROWS( DataTickets )

Closed entries in period: here we have to use the inactive relationship

Closed tickets in period = 
CALCULATE(
    COUNTROWS( DataTickets ),
    USERELATIONSHIP( 'Calendar'[Date] , DataTickets[Date closed] ),
    DataTickets[Date closed] <> BLANK()
)

These measures will not be used directly to the final measure, but will help us understand if we get the right result.

Step 3: Create the currently raised or current Entries measure

I used the following code:

Currently raised or in progress tickets = 

VAR LastDateOfPeriod = LASTDATE( 'Calendar'[Date] )

VAR Result =
    CALCULATE(
        COUNTROWS( DataTickets ),
        
// as we will manipulate the filter on calendar, first we remove any filter on this table
        REMOVEFILTERS( 'Calendar' ),

// we keep only tickets that have been raised during or before the last date displayed in the original filter context
        DataTickets[Date raised] <= LastDateOfPeriod,

// and that are closed after this same date (or not closed at all, i.e. blank)
        IF( -- if Date closed column is empty, we use a date in the future
            ISBLANK(DataTickets[Date closed]),
            DATEVALUE("9999-12-31"), 
            DataTickets[Date closed]
        ) > LastDateOfPeriod
    )
    
RETURN Result

Note that for this measure to work, you will need to cut and cut the visuals with the columns present in the Calendar table.

Full explanation of the current event pattern:

What I described above is very inspired by what sqlbi DAX masters describe in their "Event in Progress" pattern. You can find your great item here.

I hope it helps!

Best

Thomas

[amitchandak]

@Anonymous ,

Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.