Block a filter from working

[Jo_Makin_Dev]

Hi,

I am using the below formula to work out volume of sales to date up until the maximum date selected. If a user selects 2021 it counts all the sales to date up to and including 31/12/2021. I had to use the 1 modifier to make it calculate all sales and not just those in 2021. The problem is that if a user selects something in the "Reason" filter it affects the calculation when I need it not to. 

Can anyone help please?

Count({1<Reason=,Fact_Type={'P'},DateType={'Sales'},Type={'Red'},Status={'Active'},[Sales date]={"<=$(=Date(Max(Date),'DD/MM/YYYY'))"}>}[Item])

[kaushiknsolanki · Accepted solution]

It does change because of your Max Function.

You will have to neglect the selection in Max function also. So your expression will look like below.

Count({1<Reason=,Fact_Type={'P'},DateType={'Sales'},Type={'Red'},Status={'Active'},[Sales date]={"<=$(=Date(Max({<Reason=>}Date),'DD/MM/YYYY'))"}>}[Item])

[Jo_Makin_Dev]

Thank you so much for this 🙂