URGENT - Please help build one simple dax query

[xiumi_hou]

Dear all, 

I have a date table and a case table(which store the client information, when they come, when they active, when they close etc)

I have slicer, which is  the month from the date table. For example, when I click Nov, I want to know how many client is still active on Nov 1(the begining of the slicer month). 

The criteria should be (activation date before the begining of the select month and (the closed date is null or the closed date after the begining of the month))

My main relationship between these two tables are assigned to date = date table. So when to create relatioship using activation date or closed date, it needs to use userrelationship.

Below is my case table:

[Sujit_Thakur]

Dear @xiumi_hou  ,
I have attached a solution pbix 

YOu just need to make a date table and add a month column to it .
And then relate date table's date column to activation date column of your raw data table 

thats it !!

Happy to help you
If this solves your problem please let me know with accepting this post as solution and giving a like to this post 
and remember to help others on community too


Regards 
Thakur Sujit

[xiumi_hou]

Hi Thanks for the reply. My relationship is assigned date with date table. I can only write to query to complete  this. I have some other modle within this pdix file. Can you please helpo me take a look? For example, when I selecet May, I want all the people whose activation date happem before May 1st and those people whose closed date after May 1st(or do not have a closed date). In summary, number of people who was active at May 1st. Thanks

[V-lianl-msft · Accepted solution]

Try to create a measure like this:

selected people = 
CALCULATE(
    COUNTX(
        FILTER('Table','Table'[activation date]<=MIN('Date'[Date])
        &&'Table'[assigned date]<=MIN('Date'[Date]) 
        && (ISBLANK('Table'[closed date])
         || 'Table'[closed date]>MIN('Date'[Date]))),
        ('Table'[Person Name])),
    CROSSFILTER('Table'[assigned date],'Date'[Date],None)
    )

Sample .pbix

[xiumi_hou]

UP

[xiumi_hou]

UP