Calculating Rolling Days - Blank Date field - not working

prbibuilder — Tue, 04 Jun 2019 13:58:42 GMT

Hello BI Group,

I am attempting to create a rolling day count of open & closed tickets, based on the fact that the

the open tickets date column is BLANK and the closed date field is not.

My first measure to count the number of closed tickets is (and is calculating the correct number) :

Count Closed Tickets = CALCULATE([Count HelpDesk Rows], FILTER(HelpDesk, HelpDesk[Resolution Date] <> 0))

This comes up with the correct number.

However, when I attempt to create an additional measure to calculate the number of closed tickets

for the last 90 days (Rolling), the results are scewed.

Here is the second measure.

ClosedTicketsRolling90 = CALCULATE([Count CLosed Tickets], DATESINPERIOD(DateDim[Date], MAX(DateDim[Date]), -90, DAY))

MAX(DateDim[Date]) is always current date.

Thanks in advance.

Re: Calculating Rolling Days - Blank Date field - not working

prbibuilder — Tue, 04 Jun 2019 13:59:29 GMT

This solution is actually for calculation a rolling day count of Open and Closed tickets in a help desk system.

First, I had to create one measure to count the number of rows in the table helpdesk.

Two, I had to create a measure that counted the number of open tickets by their status' (status column)

( I had to dig in the data to see what ticket categories were for open, and then for closed tickets.

Open = Resolution Date is NOT populated.

Closed = A populated resolution date.

I had to create two DateDIM tables, one for open tickets, and the other for closed tickets.

You need a relationship between the DateDim table and the helpdesk table joined by the creation date.

You need a relationship from the second Date2Dim table and the helpdesk table's resolution date.

After creating the above measures, additional table, and the relationships, the below measures worked correctly.

HelpDesk Open 7 Days = CALCULATE([HelpDesk-CountOpenTx], DATESINPERIOD(DateDim[Date], MAX(DateDim[Date]), -7, DAY))

HelpDesk Closed 7 Days = CALCULATE([HelpDesk-CountClosedTx], DATESINPERIOD(DateDim2[Date], MAX(DateDim2[Date]), -7, DAY))

I hope this helps someone out in the future.