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

Advocate II

8 years ago

Please help to advise. Based on the ticket table, in Jan 2018, it has 5 ope ticket but one of them (ticket No 5) was closed on the same day. Hence it should has 4 open tickent in Jan 2018? Not sure did I intepret correctly?

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)