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

The following DAX measure was developed but it still doesn't total correctly:

TotalSalesAmount3 =
VAR SelectedDate =
SELECTEDVALUE('Date Selection'[Date Selection])
VAR MinDate =
SWITCH (
SelectedDate,
"Current Month", DATE(YEAR(MAX('Open Orders'[ShipDate])), MONTH(MAX('Open Orders'[ShipDate])), 1),
"Current Year", DATE(YEAR(MAX('Open Orders'[ShipDate])), 1, 1),
"Current Week", MIN('Date'[Day]),
"Current Quarter", DATE(YEAR(MAX('Open Orders'[ShipDate])), SWITCH(CEILING(MONTH(MAX('Open Orders'[ShipDate]))/3,1),1,1,2,4,3,7,4,10), 1),
"Last 2 Years", DATE(YEAR(TODAY())-1, MONTH(MAX('Open Orders'[ShipDate])), DAY(MAX('Open Orders'[ShipDate]))),
"Today", TODAY(),
BLANK()
)
VAR MaxDate =
SWITCH (
SelectedDate,
"Current Month", EOMONTH(MinDate,0),
"Current Year", DATE(YEAR(MAX('Open Orders'[ShipDate])), 12, 31),
"Current Week", MAX('Date'[Day]),
"Current Quarter", EOMONTH(DATE(YEAR(MAX('Open Orders'[ShipDate])), SWITCH(CEILING(MONTH(MAX('Open Orders'[ShipDate]))/3,1),1,3,2,6,3,9,4,12), 1), 0),
"Last 2 Years", TODAY(),
"Today", TODAY(),
BLANK()
)
VAR PriorMonthStartDate =
CALCULATE (
DATE(YEAR(MinDate), MONTH(MinDate) - 1, 1),
ALLSELECTED('Date Selection')
)
VAR PriorMonthEndDate =
CALCULATE (
EOMONTH(PriorMonthStartDate, 0),
ALLSELECTED('Date Selection')
)
VAR OpenOrderSales =
CALCULATE (
SUM('Open Orders'[OpenOrderLineAmount]),
FILTER (
ALL('Open Orders'[ShipDate]),
'Open Orders'[ShipDate] >= MinDate && 'Open Orders'[ShipDate] <= MaxDate
)
)
VAR InvoiceSales =
CALCULATE (
SUM('Invoices'[InvoiceLineAmount]),
FILTER (
ALL('Invoices'[InvoiceDate]),
'Invoices'[InvoiceDate] >= MinDate && 'Invoices'[InvoiceDate] <= MaxDate
)
)
VAR PriorMonthAmt =
CALCULATE (
SUM('Open Orders'[OpenOrderLineAmount]),
FILTER (
ALL('Open Orders'[ShipDate]),
'Open Orders'[ShipDate] >= PriorMonthStartDate && 'Open Orders'[ShipDate] <= PriorMonthEndDate
)
)
RETURN
OpenOrderSales + InvoiceSales + PriorMonthAmt