Open Tickets

Thanks to Phil_Seamark's insightful guidance and examples in his fantastic new book, Beginning DAX with Power BI: The SQL Pro’s Guide to Better Business Intelligence, I finally "get" the GENERATE fun...

tickets.pbix94 KB

Mathematical

Time Intelligence

Totals

Greg_Deckler

Community Champion

8 years ago

Good catch, you can fix it via a small tweak:

Tickets Open = 
VAR tmpTickets = ADDCOLUMNS('Tickets',"Effective Date",IF(ISBLANK([Closed Date]),TODAY(),[Closed Date]))
VAR tmpTable =  
SELECTCOLUMNS(
    FILTER(
        GENERATE(
            tmpTickets,
            'Calendar'
        ),
        AND(
            [Date] >= [Opened Date] && [Date] <= [Effective Date],
            NOT([Opened Date]=[Effective Date])
        )
    ),
    "ID",[Ticket Num],
    "Date",[Date]
)
VAR tmpTable1 = GROUPBY(tmpTable,[ID],"Count",COUNTX(CURRENTGROUP(),[Date]))
RETURN COUNTROWS(tmpTable1)

Advocate II

8 years ago

Hi Greg,

Thanks for the tweak. But it seem like you may have to tweak again.

Take for example in month of Feb 2018.

4 open ticket from Jan 2018 was carry forward to Feb 2018. With ticket ID 3 closed in Feb 2018. So remaining Open ticket count = 3 in Feb 2018.

3 new open ticket in Feb 2018. But 2 tickets was closed in Feb 2018. So remaining Open ticket count = 1

Total open ticket in Feb 2018 should be 3+1= 4?

Please correct me if my interpreting was wrong.

Best Regard.