How to create a table calculate OPEN CLOSED and ACTIVE AT END OF DAY Ticket count

[BI_Analyticz]

Hi Friends,

I am pretty new to Power BI. I have a simple data with Ticket ID, CreatedDate, ClosedDate and Status.

I have to show the count of tickets created, closed and what was the total count of open tickets at the end of each day. Please help me to achieve this in Power BI.

DATA Looks like Below:

ID	Status	CreatedDate	ClosedDate
A1	Open	15-Jul-20
A2	Closed	15-Jul-20	15-Jul-20
A3	Closed	15-Jul-20	15-Jul-20
A4	Closed	15-Jul-20	15-Jul-20
A5	Closed	15-Jul-20	15-Jul-20
A6	Closed	15-Jul-20	16-Jul-20
A7	Closed	16-Jul-20	16-Jul-20
A8	Closed	16-Jul-20	16-Jul-20
A9	Closed	16-Jul-20	16-Jul-20
A10	Closed	16-Jul-20	16-Jul-20
A11	Closed	16-Jul-20	17-Jul-20
A12	Closed	16-Jul-20	17-Jul-20
A13	Open	16-Jul-20
A14	Open	16-Jul-20
A15	Open	17-Jul-20
A16	Open	17-Jul-20
A17	Open	17-Jul-20
A18	Open	17-Jul-20
A19	Open	17-Jul-20
A20	Open	17-Jul-20

RESULT EXPECTED:

Date	Opened	Closed	Active at End of Day
15-Jul-20	6	4	2
16-Jul-20	8	5	5
17-Jul-20	6	2	9

[amitchandak]

@BI_Analyticz , you need to do it date table with multiple join and userelation.

Refer to this blog on a similar topic

https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-tr...

[az38]

Hi @BI_Analyticz 

try to create a new calculated table like

New Table = 
ADDCOLUMNS(
 CALENDAR(MIN(Table[CreatedDate]), TODAY()),
 "Tickets Number",
 CALCULATE(COUNTROWS(Table), FILTER(Table, Table[CreatedDate] <= EARLIER([Date]) && (Table[ClosedDate] >= EARLIER([Date]) || ISBLANK(Table[ClosedDate]))) )
)

and put this table fields into a visual 

[BI_Analyticz]

Thank you so much for the prompt response. But I am getting this as a result. I dont know how to proceed. I have seen some post of using a calendar table but I dont have knowledge on that too. Can you please help.

[BI_Analyticz]

@amitchandak 

Opened and Closed count is peectly coming. But active at end of day count is short by 1. Any heads up please?

[Anonymous]

Hi, You could also try creating 3 measures as follows...

Note : I copied your sample data into my model and gave a table name as "Tickets"

Measure 1: Opened

Opened =
VAR SelectedDates =
    VALUES ( Tickets[CreatedDate] )
VAR FilteredTable =
    FILTER ( ALL ( Tickets ), Tickets[CreatedDate] IN SelectedDates )
RETURN
    COUNTROWS ( FilteredTable )

 Measure 2: Closed

Closed =
VAR SelectedDate =
    VALUES ( Tickets[CreatedDate] )
VAR FilteredTable =
    FILTER ( ALL ( Tickets ), Tickets[ClosedDate] IN SelectedDate )
RETURN
    COUNTROWS ( FilteredTable )

Measure 3: Active at End of Day

ActiveAtEndOfDay =
VAR LastD =
    MAX ( Tickets[CreatedDate] )
VAR FilteredTable =
    FILTER (
        ALL ( Tickets ),
        Tickets[CreatedDate] <= LastD
            && ( Tickets[Status] = "Open"
            || Tickets[ClosedDate] > LastD )
    )
RETURN
    COUNTROWS ( FilteredTable )

Then plot the the "Tickets[CreatedDate]" on rows and these 3 measures to the values section of a matrix visual. You will get the following visual.

[BI_Analyticz]

@Anonymous 

Thanks for this amazing measures. But when I added one more column to my data like Team Name and want to see the breakup of it then it is not working. Do I have to do anything else.

ID	Status	CreatedDate	ClosedDate	Team
A1	Open	15-Jul-20		Tier 1
A2	Closed	15-Jul-20	15-Jul-20	Tier 1
A3	Closed	15-Jul-20	15-Jul-20	Tier 2
A4	Closed	15-Jul-20	15-Jul-20	Tier 2
A5	Closed	15-Jul-20	15-Jul-20	Tier 3
A6	Closed	15-Jul-20	16-Jul-20	Tier 3
A7	Closed	16-Jul-20	16-Jul-20	Tier 3
A8	Closed	16-Jul-20	16-Jul-20	Tier 3
A9	Closed	16-Jul-20	16-Jul-20	Tier 3
A10	Closed	16-Jul-20	16-Jul-20	Tier 3
A11	Closed	16-Jul-20	17-Jul-20	Tier 3
A12	Closed	16-Jul-20	17-Jul-20	Tier 3
A13	Open	16-Jul-20		Tier 3
A14	Open	16-Jul-20		Tier 3
A15	Open	17-Jul-20		Tier 3
A16	Open	17-Jul-20		Tier 3
A17	Open	17-Jul-20		Tier 3
A18	Open	17-Jul-20		Tier 3
A19	Open	17-Jul-20		Tier 3
A20	Open	17-Jul-20		Tier 3

Result is : 

5-Jul Tier 1 6 Tickets

5-Jul Tier 2 6 Tickets

5-Jul Tier 3 6 Tickets

[Anonymous]

You may add "Team" also as a filter. But there is no end it if keep on adding filters. I have just modified the earlier code with a "Team" filter also. But if you don't want to see the Team within the matrix visual, but want to slice the matrix using a "Team" slicer, then you could use ALLSELECTED() instead of ALL() and avoid passing filters explicitly in the DAX code. Please see the documentation of ALLSELECTED() for better understanding.

Opened2 =
VAR SelectedDates =
    VALUES ( TicketsWithTeams[CreatedDate] )
VAR SelectedTeams =
    VALUES ( TicketsWithTeams[Team] )
VAR FilteredTable =
    FILTER (
        ALL ( TicketsWithTeams ),
        TicketsWithTeams[CreatedDate] IN SelectedDates
            && TicketsWithTeams[Team] IN SelectedTeams
    )
RETURN
    COUNTROWS ( FilteredTable )

Closed2 =
VAR SelectedDates =
    VALUES ( TicketsWithTeams[CreatedDate] )
VAR SelectedTeams =
    VALUES ( TicketsWithTeams[Team] )
VAR FilteredTable =
    FILTER (
        ALL ( TicketsWithTeams ),
        TicketsWithTeams[ClosedDate] IN SelectedDates
            && TicketsWithTeams[Team] IN SelectedTeams
    )
RETURN
    COUNTROWS ( FilteredTable )

ActiveAtEndOfPeriod =
VAR LastD =
    MAX ( TicketsWithTeams[CreatedDate] )
VAR SelectedTeams =
    VALUES ( TicketsWithTeams[Team] )
VAR FilteredTable =
    FILTER (
        ALL ( TicketsWithTeams ),
        TicketsWithTeams[CreatedDate] <= LastD
            && TicketsWithTeams[Team] IN SelectedTeams
            && ( TicketsWithTeams[Status] = "Open"
            || TicketsWithTeams[ClosedDate] > LastD )
    )
RETURN
    COUNTROWS ( FilteredTable ) + 0

 The result of the above code is as follows... 

Please note that this result is based on the last sample data you posted. I have copied the same as a new table with TableName as "TicketsWithTeams"

[BI_Analyticz]

@Anonymous Thanks and a small issue. In the carry forward - Active count.

if you look at 15 July 2 are active for the days.

Next day on 16th 8 new tickets + 2 carried from 15th = 10 tickets.

Closure on 16th is 5. So the 16th End of Day count should be 5 but it is 4. Can you please help.

[Anonymous]

The 2 tickets active on 15th Jul was not pertaining to Tier 3. In Tier 3, only 1 was Active. That is why it shows 4. But if you want to show the total active irrespective of the Team, you can just comment out the the filter on "Team". Refer the code below.

ActiveAtEndOfPeriod = 
VAR LastD = Max(TicketsWithTeams[CreatedDate])
--VAR SelectedTeams = VALUES(TicketsWithTeams[Team])
VAR FilteredTable =
    FILTER(
        ALL(TicketsWithTeams),
        TicketsWithTeams[CreatedDate] <= LastD &&
  --      TicketsWithTeams[Team] in SelectedTeams &&
        (TicketsWithTeams[Status] = "Open" || TicketsWithTeams[ClosedDate]>LastD)
        )
RETURN
COUNTROWS(FilteredTable)

I have used double-hyphen (--) to comment-out the filters on team from execution. This will give the following result.

It is up to you. Feel free to modify as required. It is not necessary that you have to use measures only. You can alternatively use a calculated table also to get to the result. There are several advantages on using a calcualted table instead of measures in certain scenarios both in terms of performance as well as in terms of flexibility.  

In this case, showing 5 active against Tier 3 is wrong, if you are looking at team level. At the same time, you won't be able to list all the teams under that date, because your data does not have records for all the teams on that date. It is a limitation. You can overcome this limitation by using a calculated table.

[BI_Analyticz]

But the last column is not showing as correct for 15th July. Tier 1 closed is 1 so active should be 1. It is showing 2 for all

[Anonymous · Accepted solution]

Okay. Try this calculated table.

TicketsCalculatedTable =
VAR DateRange =
    CALENDAR ( MIN ( TicketsWithTeams[CreatedDate] ), TODAY () )
VAR Teams =
    ALLNOBLANKROW ( TicketsWithTeams[Team] )
VAR TC =
    CROSSJOIN ( DateRange, Teams )
VAR Added_Opened =
    ADDCOLUMNS (
        TC,
        "Opened", COUNTROWS (
            FILTER (
                ALLSELECTED ( TicketsWithTeams ),
                TicketsWithTeams[CreatedDate] = [Date]
                    && TicketsWithTeams[Team] = EARLIER ( TicketsWithTeams[Team] )
            )
        ) + 0
    )
VAR Added_Closed =
    ADDCOLUMNS (
        Added_Opened,
        "Closed", COUNTROWS (
            FILTER (
                ALLSELECTED ( TicketsWithTeams ),
                TicketsWithTeams[ClosedDate] = [Date]
                    && TicketsWithTeams[Team] = EARLIER ( TicketsWithTeams[Team] )
            )
        ) + 0
    )
VAR Added_Active =
    ADDCOLUMNS (
        Added_Closed,
        "ActiveAtEndOfDay", COUNTROWS (
            FILTER (
                ALLSELECTED ( TicketsWithTeams ),
                TicketsWithTeams[CreatedDate] <= [Date]
                    && TicketsWithTeams[Team] = EARLIER ( TicketsWithTeams[Team] )
                    && ( TicketsWithTeams[Status] = "Open"
                    || TicketsWithTeams[ClosedDate] > [Date] )
            )
        ) + 0
    )
RETURN
    Added_Active

[BI_Analyticz]

Thank you so much for helping this newbie... It works fine.

[az38]

@BI_Analyticz 

try this modified calendar table

Calendar Table = 
ADDCOLUMNS(
 CALENDAR(MIN('Table'[CreatedDate]), TODAY()),
 "Opened",
 CALCULATE(DISTINCTCOUNT('Table'[ID]), FILTER('Table', 'Table'[CreatedDate] = EARLIER([Date])) ),
 "Closed",
 CALCULATE(DISTINCTCOUNT('Table'[ID]), FILTER('Table', 'Table'[ClosedDate] = EARLIER([Date])) ),
 "Active at End of Day",
 CALCULATE(COUNTROWS('Table'), FILTER('Table', 'Table'[CreatedDate] <= EARLIER([Date]) && ('Table'[ClosedDate] > EARLIER([Date]) || ISBLANK('Table'[ClosedDate]))) )
)

[amitchandak]

@BI_Analyticz , I think the question is for @az38 

To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :
https://radacad.com/creating-calendar-table-in-power-bi-using-dax-functions
https://www.archerpoint.com/blog/Posts/creating-date-table-power-bi
https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/

See if my webinar on Time Intelligence can help: https://community.powerbi.com/t5/Webinars-and-Video-Gallery/PowerBI-Time-Intelligence-Calendar-WTD-Y...


Appreciate your Kudos.