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)

abehrmann

Helper II

6 years ago

How can this be adjusted to show tickets that were open and closed the same day?