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: IDStatusCreatedDateClosedDateA1Open15-Jul-20 A2Closed15-Jul-2015-Jul-20A3Closed15-Jul-2015-Jul-20A4Closed15-Jul-2015-Jul-20A5Closed15-Jul-2015-Jul-20A6Closed15-Jul-2016-Jul-20A7Closed16-Jul-2016-Jul-20A8Closed16-Jul-2016-Jul-20A9Closed16-Jul-2016-Jul-20A10Closed16-Jul-2016-Jul-20A11Closed16-Jul-2017-Jul-20A12Closed16-Jul-2017-Jul-20A13Open16-Jul-20 A14Open16-Jul-20 A15Open17-Jul-20 A16Open17-Jul-20 A17Open17-Jul-20 A18Open17-Jul-20 A19Open17-Jul-20 A20Open17-Jul-20 RESULT EXPECTED: DateOpenedClosedActive at End of Day15-Jul-2064216-Jul-2085517-Jul-20629

Anonymous

Not applicable

6 years ago

Solved

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

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

Need Help

Tips and Tricks

Anonymous

6 years ago

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

14 Replies

az38
Community Champion
6 years ago
Hi Anonymous

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
- Anonymous
  Not applicable
  6 years ago
  
  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.
  - amitchandak
    Super User
    6 years ago
    Anonymous , 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-YTD-LYTD-Week-Over-Week/m-p/1051626#M184
    
    Appreciate your Kudos.
amitchandak
Super User
6 years ago
Anonymous , 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-trend/ba-p/882970