Measure to analyze current date vs record dates

[kramaswamy]

Hi all,

Trying to figure out how to construct a measure that will return true or false based on whether the date being analyzed occurs after a record date and before another date on the same record.

Specifically - I have a table which is joined to a date table against the record creation date. I want to know if the date I'm looking at in Excel / etc... (using a hierarchy of fiscal dates (year/quarter/month)) occurs after that creation date, but before the closed date of the record.

Please let me know if the question is unclear, as I'm having trouble trying to spell it out properly. Thanks!

[kramaswamy]

Actually - now that I think about it some more, it occurs to me that this information might not really even be possible, since it wouldn't really make any sense to roll that info up - IE, if in my previous example I wanted to see one record show up for 01 Oct 2020 and two for 14 Oct 2020, I would have to decide how the month of October should handle that info, and likewise, how it continues rolling up to higher levels.

Is that something I can program as part of the measure? I know that I can use ISINSCOPE to decide which stat is being presented - I suppose I'd have to use a different stat to display depending on what is being analysed? IE, if I were looking at the daily data, I'd see what I had described in my post, whereas if I were looking at monthly/quarterly/annually, I would, for example, take the day with the highest value within that month/quarter/year?

[iXpert_info · Accepted solution]

Hey @kramaswamy 

I think this one will help you better
https://community.powerbi.com/t5/Desktop/dax-how-to-count-open-tickets/m-p/100614

Did I answer your question? Please mark this as solution

Best Regards
Jay Patel
iXpert Analytics

[kramaswamy]

Definitely points me in the right direction. Thank you!

[kramaswamy]

Hi there Jay,

Not sure if this will help, but here's basically what I'm looking to do:

That third column ("Number of Requests Closed") should basically be getting the count of records for each time block that were open at that particular time. My records have two dates - the date they were opened and the date they were closed. So - if I were looking at 01 Oct 2020, I would want to see a count of records that, as of 01 Oct 2020, were opened and not yet closed.

If I had the following five records:

RequestedOn	ClosedOn
2020-10-13 0:00	2020-10-17 15:49
2020-08-30 0:00	2020-11-17 0:00
2020-03-21 0:00	2020-03-26 13:52
2020-03-23 0:00	2020-04-06 13:02
2020-03-28 0:00	2020-04-02 7:50

For 01 Oct 2020, I would expect to see only one record show up for pending. For 14 Oct 2020, on the other hand, I would expect to see two records show up for pending.

Does this make more sense?

[iXpert_info]

Hey @kramaswamy 
is there a way you can share a sample data or sample pbix?

Best Regards
Jay Patel