Forum Discussion
allexcept
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 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.
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"))
The second solution of getting the measure driven by the filter is what I needed. Thank you so much for your help!