New and Old Clients based on Date Slicer

[Rate]

Hello!

I am having trouble with two measures related with the date selection in slicer.

In my model, I have a list of clients and the date where they became Clients. I want to have two measures, depending on the date I choose:

1. New Clients, that will only consider those that have a starting date >= selected date.

2. Old Clients, considering only those that have a starting date < selected date.

In this case, I have a Date table and the clients table, with the following structure:

Client ID	Client Name	Starting Date
1	Client 1	03/07/2019
2	Client 2	03/07/2019
3	Client 3	04/07/2019
4	Client 4	05/07/2019
5	Client 5	05/07/2019
6	Client 6	05/07/2019

In this example, i would like that my measures result as such, when selecting the date in the Header:

	03/07/2019	04/07/2019	05/07/2019	June 2019	July 2019
New Clients	2	1	3	6	0
Old Clients	0	2	3	0	6

Do you have any ideas on how I could achieve this?

Thanks a lot for your help! I am really stuck with this one.

[Ashish_Mathur · Accepted solution]

Hi,

I just created a Calendar Table and built a relationship as shown below.  Thereafter, i wrote these 2 measures.  Here's the PBI file.

New clients = COUNTROWS(Data)

Old clients = CALCULATE([New clients],DATESBETWEEN('Calendar'[Date],MINX(ALL('Calendar'),'Calendar'[Date]),MIN('Calendar'[Date])-1))

Hope this helps.

[Rate]

Hello @Ashish_Mathur ,

Tried your formulas and work amazingly!!

Thank you so so much!!

[Ashish_Mathur]

You are welcome.

[MattAllington]

Problems in DAX are almost always related to filtering. Slicers filter data - that’s what they do. Normally you would have a calendar table with a relationship to your data table date column. When you click a slicer in the calendar table, the filter flows through the relationship onto the data table. So if you think through the issue, a relationship will be your enemy. You need a calendar table with no relationship, you need to detect the date selected in the slicer from the calendar table, then apply this to the data table with a virtual  filter, something like this

=calculate(countrows(datatable),filter(all(datatable[date]),datatable[date] <= min(calendar[date]))

[Rate]

Hello @MattAllington ,

Thanks a lot for your super quick answer. I think I can only get to grasp the density of the solution your are proposing! So, please, excuse me for the following rather dumb questions!

I tried creating a new date table, not related to anything within the model, change the slicer to this table and change the formula as you proposed:

- On the great side, with your proposal, the Old Clients work amazingly.

- On the not so good side, I can´t get New Clients to work. With 
>= min(calendar[date]), whenever I choose a date from the slicer, I get the data for that date until today (as it should by means of the > in the formula).

- Also, I am getting the idea that your solutions will mean changing all the measures I have created (which is not a problem) to incorporate the virtual date filtering, but I don't know how to virtualice the measures in order to achieve what I now have via the relationship to my data table. Do you have any suggestions on this or some documentation I could review on this kind of date modelling?

Again, thanks a lot! Really appreciate your help!

[MattAllington]

If you have other measures already, the easiest option is to simple have a second date table that is not connected, just for this purpose. As for the new customer problem, I’m not sure what the issue is. The way I like to structure my thinking is to go into the data view, switch to the data table and manually apply a filter using the drop down filters so it shows the records you want. Then work out how to apply that filter inside a DAX formula. 

HTH. 

[Rate]

Hello @MattAllington ,

I spent some time reading on your blog (amazing job!!! Really, congratulations!) and on some other recommendations, and I really like the idea of a detached Calendar Table and virtual filtering. If someone finds it useful, this is the virtual filter I will be using for my measures.

    CALCULATE(
        sum(Datatable[Data]); 
        FILTER('Datatable';
        'Datatable'[Date] >= MIN('Detached Calendar'[Date]) &&
        'Datatable'[Date] <= MAX('Detached Calendar'[Date])))

Again, thanks a lot for your help and keep up the amazing work!