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
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?
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.
* Matt is a Microsoft MVP (Power BI) and author of the Power BI Book Supercharge Power BI.
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.