Count Number of Tickets Opened Each Day

[DemingPDCA]

I was able to create a count of Net tickets by day using the following formula (based on a youtube video I found), and it works GREAT!

Total DMRs = 
    CALCULATE(
        COUNT('Distinct DMR'[DMR])
        , 'Distinct DMR'[Open Date] <= MAX('Calendar'[Date])
        && ('Distinct DMR'[Close Date] > MAX('Calendar'[Date]) || ISBLANK('Distinct DMR'[Close Date]))
    )

Shows me the total tickets Open rolling by day for the last 12 months:

I want to be able to create the chart below though, and tried using this formula, but it ends up blank. How am I overcomplicating this? 😞 It feels like this one should be easier and I'm probably making it too hard! Sorry

Total Opened = 
    CALCULATE(
        COUNT('Distinct DMR'[DMR])
        , 'Distinct DMR'[Open Date] = MAX('Calendar'[Date])
    )

[sevenhills · Accepted solution]

I guess you are looking for:
a) Active .. Total DMRs
b) Opened ... Opened DMRs
c) Closed ... Closed DMRs

Per your post, you said this is working fine: (I assumed that Active is working fine)

a) Active (Same copy of your above measure, no changes)

Total DMRs =
CALCULATE(
     COUNT('Distinct DMR'[DMR])
        , 'Distinct DMR'[Open Date] <= MAX('Calendar'[Date])
          && ('Distinct DMR'[Close Date] > MAX('Calendar'[Date]) || ISBLANK('Distinct DMR'[Close Date]))
)

b) Opened

Opened =
CALCULATE(
       COUNT('Distinct DMR'[DMR]),
       USERELATIONSHIP('Calendar'[Date], 'Distinct DMR'[Open Date])
)

c) Closed

Closed =
CALCULATE(
       COUNT('Distinct DMR'[DMR]),
       USERELATIONSHIP('Calendar'[Date], 'Distinct DMR'[Close Date])
)

[DemingPDCA]

Figured it out - thank you!!! My Open Date was actually a date time, and I needed to add the .[Date] to it when I pulled it over to make sure it was not looking for Date/Time on the calendar Date!

[sevenhills]

Did it worked ? 

If yes, post the snapshot of working results, so that future members can see the completed details 🙂 Also , mark it as solution. Thanks

[DemingPDCA]

Worked great - thanks!

[DemingPDCA]

I tried this prior, but end up loosing my date somehow when I do this.

My data structure looks like this:

When I try the suggestion I get this:

But yes - you are exactly right on when I am trying to do! Thanks!

Not sure - but is it something to do with me having my relationships set up wrong?

[sevenhills]

I think the relationships are causing as wrong. I noticed the Open Date having time. I assume similarly Close Date has time for not null values.

To resolve, can you have Open Date and Close Date as "Date" data type and then do the relationships again. Same way, but as date data type.

Open Date to Calendar Date

Close Date to Calendar Date 

[DemingPDCA]

*looking at this 10 minutes ago would have been helpful 😄 

Thanks so much!

[sevenhills]

🙂

[andhiii079845]

Can you share please some more underlaying data structure?

How is opened and closed is calculated.