I’d like to add a filter in the MAXX expression because I need to filter with the Compte_Table[Compte]=70 or 71 or 74 in order to define the LastSalesDate.
Ventes YTD =
VAR LastSalesDate=MAXX(ALL(Comptes_Table[HDOCDATE]);Comptes_Table[HDOCDATE])
VAR YTDSales = TOTALYTD([Ventes];Date_Table[Date])
RETURN
IF(MIN(Date_Table[Date])<= LastSalesDate;YTDSales;BLANK())
Hi Pam,
Looking through this one.
When you say filter, I’m not 100% sure what you mean here.
I don’t really know what your model looks like here, so it’s difficult to access how filters should be naturally occurring via the relationships that you have.
Where is the filter not occurring for you?
If you have add you model to this post I’m sure it can be worked out relatively quickly.
As an alternative adding an image of your model will also be helpful.
Just also looking at the circle you’ve made, I’m not sure what this is meant to assist with. More clarity around the specific issue would be helpful for me.
Thanks
Hi sam this is my file.
Also if I select 2018 and Q1+Q2+Q3
then the total Budget YTD is the total of the year and NOT the total of the Q1+Q2+Q3
I’ve worked through this is quite a bit of detail. I have found it hard to understand everything but I think I have enough of an idea to give you some pointers.
First I think that it can be simplified quite a bit.
The first things I did was clean up the model and added a Comptes index table.
You want this because you need to be able to filter both your actuals and budgets table at the same time. In you prepared model you couldn’t do that because your slicer was coming from the Comptes table itself and there was no relationship with the budget table.
See below
I used a simple VALUES function as a calculated table to create this.
Now the slicer inside your report should come from this table
One things I don’t really understand is why so many formulas here
I’m not sure why these would be required. Can’t you get similar calculations with just a simple sum of the columns.
Maybe the new model setup will help you here.
I’m pretty confident you don’t need many of the DAX measures that have been create. Most of the calculations can be created with just the right model setup and then correct filters and slicer within your report page.
I’ve added the attached model here to continue working on.
Let me know if any questions from here.
I’m not sure if I’ve answered your question specifically but this is where I can get to without more information required around what you’re looking to achieve. Hopefully this give you some ideas to work on though
Thx Sam for your great Help, I will follow your advice.
please could you remove the pbix file because there is some real data from my customer on it.
thx
regards
Ok great.
File removed.
