allexcept

[Anonymous]

I am trying to create a measure that calculates total sales within a range of selected dates that are calculated within the measure. The table groups sales by the latest possible date in the range. Here is the measure:

SalesInRange = 
VAR
MaxDate = EOMONTH(SELECTEDVALUE(Date_Dimension[MonthYear]),0)
VAR
MinDate = EOMONTH(SELECTEDVALUE(Date_Dimension[MonthYear]),-[Variable])
RETURN
CALCULATE(SUM(Sales[Sales]), FILTER(ALLEXCEPT(Test2, Test2[BranchCode]), Test2[DateofSale]<=MaxDate && Test2[DateofSale]>MinDate && Test2[NewRenew]="R"))

The variable measure is the number of months that determines when to start the date range.

The problem is that I want the measure to filter out renewed sales within the calculation, but I wanted the branch office to be able to be selected in a filter on the dashboard. If I change the calculation to: 

CALCULATE(SUM(Sales[Sales]), ALL(Sales),Sales[DateofSale]<=MaxDate,Sales[DateofSale]>MinDate, Sales[NewRenew]="R", Sales[BranchCode]="A")

where the branch is filtered within the measure, it works fine. But I do not want to filter within the measure I want to slice it on the dashboard. Any help would be appreciated. Thanks!

[Anonymous]

@Anonymous Can you create a mock data file and post it here?  I am just having some trouble understanding what you want to do. If you can post some sample data sets and results I can probably give you the formulae.

[Anonymous]

https://mysimpson-my.sharepoint.com/:u:/g/personal/devon_veach_my_simpson_edu/EStRBWi9441Cs4M_ea3QKs...

[Anonymous · Accepted solution]

Hello @Anonymous, I am not totally sure if I get the question, but I put in all combinations I think you might need. Let me know if I still didn't ans your question.

If you want to find sales per branch no matter what filter is appliedthen you can use this formula:

SalesTest1 =
VAR MaxDate = EOMONTH(SELECTEDVALUE(Date_Dimension[MonthYear]),0)
VAR MinDate = EOMONTH(SELECTEDVALUE(Date_Dimension[MonthYear]),-[Variable])
RETURN CALCULATE(SUM(Sales[Sale]), FILTER(ALL(Sales),Sales[DateofSale]<=MaxDate && Sales[DateofSale]>MinDate && Sales[NewRenew]="R"),ALLEXCEPT(Sales,Sales[Branch]))

if you want it driven by a filter then this:

SalesTest1 =
VAR MaxDate = EOMONTH(SELECTEDVALUE(Date_Dimension[MonthYear]),0)
VAR MinDate = EOMONTH(SELECTEDVALUE(Date_Dimension[MonthYear]),-[Variable])
RETURN CALCULATE(SUM(Sales[Sale]), FILTER(ALLSELECTED(Sales),Sales[DateofSale]<=MaxDate && Sales[DateofSale]>MinDate && Sales[NewRenew]="R"))

[Anonymous]

The second solution of getting the measure driven by the filter is what I needed. Thank you so much for your help!