Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
8 years ago
Solved

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. 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's avatar
    Anonymous
    8 years ago

    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"))

4 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    8 years ago

    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's avatar
        Anonymous
        Not applicable
        8 years ago

        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"))