cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Rate Regular Visitor
Regular Visitor

New and Old Clients based on Date Slicer

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 IDClient NameStarting Date
1Client 103/07/2019
2Client 203/07/2019
3Client 304/07/2019
4Client 405/07/2019
5Client 505/07/2019
6Client 605/07/2019

 

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

 03/07/201904/07/201905/07/2019June 2019July 2019
New Clients21360
Old Clients02306

 

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

 

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

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
Super User
Super User

Re: New and Old Clients based on Date Slicer

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.

Untitled.png

7 REPLIES 7
Super User
Super User

Re: New and Old Clients based on Date Slicer

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]))



* Matt is a Microsoft MVP (Power BI) and author of the Power BI Book Supercharge Power BI.
Rate Regular Visitor
Regular Visitor

Re: New and Old Clients based on Date Slicer

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!

Super User
Super User

Re: New and Old Clients based on Date Slicer

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. 



* Matt is a Microsoft MVP (Power BI) and author of the Power BI Book Supercharge Power BI.
Super User
Super User

Re: New and Old Clients based on Date Slicer

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.

Untitled.png

Highlighted
Rate Regular Visitor
Regular Visitor

Re: New and Old Clients based on Date Slicer

Hello @Ashish_Mathur ,

 

Tried your formulas and work amazingly!!

 

Thank you so so much!!

Rate Regular Visitor
Regular Visitor

Re: New and Old Clients based on Date Slicer

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!

Super User
Super User

Re: New and Old Clients based on Date Slicer

You are welcome.

Helpful resources

Announcements
Community News & Announcements

Community News & Announcements

Get your latest community news and announcements.

Summit North America

Power Platform Summit North America

Register by September 5 to save $200

Virtual Launch Event

Microsoft Business Applications Virtual Launch Event

Watch the event on demand for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Users Online
Currently online: 6 members 3,095 guests
Please welcome our newest community members: