Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Solved! Go to 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.
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.
You are welcome.
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]))
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!
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.
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!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
96 | |
93 | |
82 | |
70 | |
64 |
User | Count |
---|---|
118 | |
106 | |
93 | |
79 | |
72 |