Forum Discussion

CJLittle's avatar
CJLittle
Frequent Visitor
5 years ago
Solved

need help with getting my data visualized properly

Hello all. I have some data that is in a format similar to the following:   Ticket Number Opened Date Closed Date 1 4/6/2021 5/2/2021 2 4/6/2021 4/7/2021 3 4/8/2021 4/15/2021 ...
Need Help
  • v-xiaotang's avatar
    v-xiaotang
    5 years ago

    thanks for your reply CJLittle 

    sure, try to change 'Table'[Closed Date] >= _seldate to Table'[Closed Date] > _seldate  in measures

    count = 
var _seldate=SELECTEDVALUE(calendarTable[Date])
return countrows(filter('Table', 'Table'[Opened Date]<=_seldate &&'Table'[Closed Date]>_seldate))

    result

     

     

    Best Regards,

    Community Support Team _Tang

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

CJLittle's avatar
CJLittle
Frequent Visitor
5 years ago

This seems like it might be closer to what I want but isn't taking into account the fact that ticket number 3 was closed on 4/15, reducing the total number of tickets open on 4/15 to 3 from 4.

v-xiaotang's avatar
v-xiaotang
Community Support
5 years ago

thanks for your reply CJLittle 

sure, try to change 'Table'[Closed Date] >= _seldate to Table'[Closed Date] > _seldate  in measures

count = 
var _seldate=SELECTEDVALUE(calendarTable[Date])
return countrows(filter('Table', 'Table'[Opened Date]<=_seldate &&'Table'[Closed Date]>_seldate))

result

 

 

Best Regards,

Community Support Team _Tang

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

  • CJLittle's avatar
    CJLittle
    Frequent Visitor
    5 years ago

    Thank you so much. This got me almost all the way there. I just had to add in an or statement around the closed date to check for null values too. The final measure looks like the following:

     

    count = 
var _seldate=SELECTEDVALUE(calendarTable[Date])
return countrows(filter('Table', 'Table'[Opened Date]<=_seldate && ('Table'[Closed Date]>_seldate || 'Table'[Closed Date] = BLANK())))