Help with formula counting by dates

[Anonymous]

Hello, 

I posted an earlier problem of counting clients based on whether they started a project or they had a project interaction based on different date ranges. 

Someone solved it with the following formula which i think is correct and has been working. 

Total clients by start date or casenote =

VAR _Start = FIRSTDATE(Calender[Date])

VAR _End = LASTDATE(Calender[Date])

VAR _Clients =

CALCULATETABLE(

VALUES('All projects'[Client Record ID]),

'All projects'[Project Start Date] >= _Start,

'All projects'[Project Start Date] <= _End)

VAR _CLientNotes =

CALCULATETABLE(

VALUES('All projects'[Client Record ID]),

'All Casenotes'[Date of Session] >= _Start,

'All Casenotes'[Date of Session] <= _End,

CROSSFILTER('All projects'[Project Record ID],'All Casenotes'[Project Record ID],Both))

RETURN

COUNTROWS ( DISTINCT ( UNION ( _Clients, _CLientNotes )))

However, I noticed today that this has been counting clients that do not fit into the date criteria. For example. The date range in the below is 01/04/2019 - 31/03/2020, but as you can see the forumla is counting clients that do not fit into this range. 

Not sure what is wrong here? I changed the date settings in the load to use Locale (UK) but didn't change anything. There are no links between the calandar table and anything else so i don't think filtering is a problem either?

Any suggestions on what's wrong would be very welcome!

Thanks 

[Icey]

Hi @Anonymous ,

Is this problem solved?

If it is solved, please always accept the replies making sense as solution to your question so that people who may have the same question can get the solution directly.

If not, please let me know.

Best Regards,

Icey

[Anonymous]

Hi Icey, 

Unfortunately i'm still struggling with this. I'm checking it by pulling in project name and start date from the all projects table, and casenotes session date from the all casenotes table and then finally the measure. 

I'm then filtering with the calander table and it still seems to be counting casenotes that are outside the filtered range. 

Any suggestions?

Thanks 

[Icey]

Hi @Anonymous ,

---

Not sure what is wrong here? I changed the date settings in the load to use Locale (UK) but didn't change anything. There are no links between the calandar table and anything else so i don't think filtering is a problem either?

---

There is no relationship between 'Date's table and "All Casenotes" table. So 'Dates'[Date] slicer will not filter 'All Casenotes'[Date of Session].

Best Regards,

Icey

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

[amitchandak]

@Anonymous , Can share the logic. Can you share sample data and sample output in table format?

refer if this can he any help : https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-trend/ba-p/882970

[Anonymous]

Hi 

Here is the original topic that this expression came from. 

https://community.powerbi.com/t5/Desktop/count-distinct-based-on-two-date-using-OR/m-p/1145546#M5198...

In terms of providing sample data. Do you mean to export some data from the three relevant tables (client / project / interaction) or would screen shots suffice?

Thanks