Forum Discussion
allexcept
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. H...
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"))
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!