Forum Discussion

Blevels's avatar
Blevels
Frequent Visitor
3 years ago
Solved

Add Slicer Filter Measure to Non-Slicer Filter Measure

Hello, How does one create a measure that adds a Slicer Filtered Measure to a Non-Slicer measure?   Say, that a Slicer Filtered Measure is defined as:  Sales Invoices = CALCULATE(             S...
  • Blevels's avatar
    Blevels
    3 years ago

    I was able to come to a solution.  I don't believe that the solution is the best way to handle the calculation.

    Here is what was done:  Create a duplicate table to 'Open Orders' , then calculate the static value needed.
    The problem is when adding two measures, one being dynamic based on a slicer and the other static. 

    Created a duplicate table called:  'Open Orders II'

    Calculation:

    VAR SelectedDate = SELECTEDVALUE('Date Selection'[Date Selection])
    VAR MinDate = CALCULATE(MIN('Date Selection'[Date]), ALL('Date Selection'))
    VAR MaxDate = CALCULATE(MAX('Date Selection'[Date]), ALL('Date Selection'))
    VAR PriorMonthStart = DATE(YEAR(MinDate), MONTH(MinDate)-1, 1)
    VAR PriorMonthEnd = EOMONTH(PriorMonthStart,-1)
    VAR PriorMonthAmt =        
            CALCULATE(
                SUM('Open Orders II'[OpenOrderLineAmount]),
                FILTER(ALL('Open Orders II'), 1=1),            
                FILTER(ALL('Open Orders II'),
                        'Open Orders II'[ShipDate] >= EOMONTH(today(),-2)+1
                        && 'Open Orders II'[ShipDate] <= EOMONTH(today(),-1)
                        )
                    )
    VAR OpenOrderSales =
        CALCULATE(
            SUM('Open Orders'[OpenOrderLineAmount]),
            FILTER(
                ALL('Date'),
                'Date'[Day] >= MinDate && 'Date'[Day] <= MaxDate
            )
        )
    VAR InvoiceSales =
        CALCULATE(
            SUM('Invoices'[InvoiceLineAmount]),
            FILTER(
                ALL('Date'),
                'Date'[Day] >= MinDate && 'Date'[Day] <= MaxDate
            )
        )
    RETURN  IF(SelectedDate = "Current Month", OpenOrderSales + InvoiceSales + PriorMonthAmt, OpenOrderSales + InvoiceSales)
Blevels's avatar
Blevels
Frequent Visitor
3 years ago

I was able to come to a solution.  I don't believe that the solution is the best way to handle the calculation.

Here is what was done:  Create a duplicate table to 'Open Orders' , then calculate the static value needed.
The problem is when adding two measures, one being dynamic based on a slicer and the other static. 

Created a duplicate table called:  'Open Orders II'

Calculation:

VAR SelectedDate = SELECTEDVALUE('Date Selection'[Date Selection])
VAR MinDate = CALCULATE(MIN('Date Selection'[Date]), ALL('Date Selection'))
VAR MaxDate = CALCULATE(MAX('Date Selection'[Date]), ALL('Date Selection'))
VAR PriorMonthStart = DATE(YEAR(MinDate), MONTH(MinDate)-1, 1)
VAR PriorMonthEnd = EOMONTH(PriorMonthStart,-1)
VAR PriorMonthAmt =        
        CALCULATE(
            SUM('Open Orders II'[OpenOrderLineAmount]),
            FILTER(ALL('Open Orders II'), 1=1),            
            FILTER(ALL('Open Orders II'),
                    'Open Orders II'[ShipDate] >= EOMONTH(today(),-2)+1
                    && 'Open Orders II'[ShipDate] <= EOMONTH(today(),-1)
                    )
                )
VAR OpenOrderSales =
    CALCULATE(
        SUM('Open Orders'[OpenOrderLineAmount]),
        FILTER(
            ALL('Date'),
            'Date'[Day] >= MinDate && 'Date'[Day] <= MaxDate
        )
    )
VAR InvoiceSales =
    CALCULATE(
        SUM('Invoices'[InvoiceLineAmount]),
        FILTER(
            ALL('Date'),
            'Date'[Day] >= MinDate && 'Date'[Day] <= MaxDate
        )
    )
RETURN  IF(SelectedDate = "Current Month", OpenOrderSales + InvoiceSales + PriorMonthAmt, OpenOrderSales + InvoiceSales)