Getting a count of date A existing between min/max of date B

[Red_prov]

Hello, 

So i am currently building a report related to tickets. 

I have a request to show several counts related to the ticket data. 

First is Tickets Opened, Second is comments on tickets, third is closures.

Currently i have two dates Created-Date and Modified-Date. Created is the timestamp when the ticket was created in the system. the modified date is the points in time when the ticket had any changes comments, reassignment, etc.

When using the modified date as a slicer i get the total of tickets touched by the user, their comments, and their closures within the given date range.

when using the created date as a slicer i get the total for tickets created, comments on those tickets, closures on those tickets. 

so i am trying to use one slicer in my query to show the total unique count of tickets created within my date range, while also showing the total comments and closures across all tickets within the date range while using the same slicer and not having two slicers each related to their own visuals. 

I thought maybe in DAX there may be a way to use the modified date as the slicer. But somehow calculate how many records CREATE datetime falls between the min/max?

I'm open to other suggestions. I also tried to create a date table and duplicate the query and use the date table as the parent and join each query on one of the date fields CREATED/MODIFIED. However this did not work either.

I'm not sure how to accomplish displaying counts of total tickets created based soley off the datetime field while also including unique modifications off its own datetime field.

Example of the two counts posted below. image one is counts when slicing on the creation date

Here between 2/19 and 2/22 we have 34 tickets open, all were closed and 36 comments. being slicing on created date is showing field changes related to the created ticket list.

But if i swap the slicer to the date field related to the changes. the count increases due to this displaying unqiue tickets touched on this date range (51),  unique comments made within the date range regardless of the creatoin date, and unique closures.

So im trying to keep a count of how many tickets were created within this range while also displaying all other changes unfiltered by just ones created within that range. so a mixture of the two scenarios above. Even if i was to keep the 51 unique instances were a ticket had been modified but also displaying the 34 count of unqiue tickets also created within the range.

Trying to provide as much info as possible so sorry if im spinning in circles.

thanks for any advice/solutions

[Red_prov · Accepted solution]

so i believe was able to solve this count issue using DAX USERELATIONSHIP with an inactive relationship. my active relationship is on the TIMESTAMP date field to a calendar table i made of dates.

Then i used this on a card visual

TotalCreated = CALCULATE(DISTINCTCOUNT(Query1[ID]),USERELATIONSHIP('Calendar'[Date],Query1[CREATED]))

[v-rongtiep-msft]

Hi @Red_prov ,

 Given the complexity of your tables and relationships , I cannot understand your meaning correctly. Could you please provide your pbix file without privacy information and desired output with more details.

How to Get Your Question Answered Quickly 

Best Regards

Community Support Team _ Polly

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Red_prov]

@v-rongtiep-msft 

So it appears the measure is doing 1s/0s however they arent counting/suming

Heres another measure i created

CreatedCount1 =

VAR minselect =

    CALCULATE ( MIN ( 'Date Table'[Date] ), ALLSELECTED ( 'Date Table'[Date] ) )

VAR maxselect =

    CALCULATE ( MAX ( 'Date Table'[Date] ), ALLSELECTED ( 'Date Table'[Date] ) )

RETURN

    if(

    SELECTEDVALUE('Query1'[CREATED])>=minselect

    &&

    SELECTEDVALUE('Query1'[CREATED])<=maxselect,

1,0)

Heres a screenshot of the data sorted to display the count is working to show 1s when slicer is between 10/1/22-10/31/22

However even with the table version appearing to display the correct info and 1s when its in the date range. the sum of these doesnt appear to be working

[Red_prov]

oddly besides not being able to get a total. and in the original screenshot above i posted showing 10/1 on creation has a 1 recorded compared to 9/30 displaying 0.

It appears the higher end of the slicer 10/31 is not counting? 

[Red_prov]

I'm unable to.

I am just trying to count how many times date X is present in DateY start/end if date Y is the slicer. so if i have a slicer of DATE-Y set for the month of janurary. I want to determine how many times DATE-X column has records that fall within the slicer for DATe-Y

[Red_prov]

@v-rongtiep-msft I've attempted the following 2 measures with no success.

InDateRange =

var _rangeStart=MIN('Query1'[TIMESTAMP])

var _rangeEnd=MAX('Query1'[TIMESTAMP])

return

if(

    SELECTEDVALUE('Query1'[CREATED])>=_rangeStart

    &&

    SELECTEDVALUE('Query1'[CREATED])<=_rangeEnd

    &&

    SELECTEDVALUE('Query1'[CREATED])<>BLANK(),

1,0)

This is just outputting 0 when attempting to count create between timestamp.

Also i tried this however im getting unexpected results. 

CreatedCount = Calculate(Countrows(Query1), FIlter (Query1,Query1[CREATED] >= Min('Date Table'[Date]) && Query1[CREATED]<= MAx('Date Table'[Date])))

I'm trying to mimic this with only one slicer

Currently to have this image. i have the count of CREATEd tied specifically to the lefthand side slicer CREATEd. while the rest of the visuals on the page are tied to the TIMESTAMP dateTime slicer