Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
Red_prov
Helper I
Helper I

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

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.

Red_prov_2-1668723582219.png

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.

Red_prov_3-1668724003548.png

 

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

 

 

 

 

1 ACCEPTED SOLUTION
Red_prov
Helper I
Helper I

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]))
 

View solution in original post

7 REPLIES 7
Vinutha
Frequent Visitor

i am in the similar boat ,just want to check if this got resolved?

Red_prov
Helper I
Helper I

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]))
 
Anonymous
Not applicable

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.

@Anonymous 

 

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
Red_prov_0-1669220127500.png

 

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

 

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_0-1669222100270.png

 

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

@Anonymous 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
Red_prov_0-1669131214583.png

 

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

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

Check out the October 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

Find out what's new and trending in the Fabric Community.