topic Re: Add Slicer Filter Measure to Non-Slicer Filter Measure in DAX Commands and Tips

Add Slicer Filter Measure to Non-Slicer Filter Measure

Blevels — Mon, 13 Mar 2023 15:57:14 GMT

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(

SUM('Invoices'[InvoiceLineAmount]),

DATESBETWEEN('Open Order (Current) Measures'[ShipDate],

CALCULATE(

MIN('Date'[Day]),

FILTER('Date', 'Date'[Day] = ('Date'[Day]))),

CALCULATE(

MAX('Date'[Day]),

FILTER('Date', 'Date'[Day] = ('Date'[Day]))))

)

And a NON-Slicer Measure is defined as:

Prior Sales Orders =

CALCULATE(

SUM('Open Order (Current) Measures'[OpenOrderLineAmount]),

DATESBETWEEN('Open Order (Current) Measures'[ShipDate],

CALCULATE(

MIN('Date'[Day]),

FILTER('Date', 'Date'[Day] = EOMONTH(today(),-2)+1)),

CALCULATE(

MAX('Date'[Day]),

FILTER('Date', 'Date'[Day] = EOMONTH(today(),-1))))

)

What should be the DAX statement that adds: [Sales Invoices] + [Prior Sales Orders]?
Here the slicer selection should filter [Sales Invoices] so the Edit Interactoins are selected for

the Card that is holding [Sales Invoices]. Likewise the Card holding [Prior Sales Orders] is None.

Creating a new Card for this new measure to add these meaures together doesn't return the correct

results as the [Prior Sales Orders] returns a blank value when Slicer interactions is turned on but when

turned off the [Sales Invoices] doesn't filter.

Example: [Prior Sales Orders] = 1000 and the Filter [Sales Invoices] = 10,000 so the new Card measure should

show the value of 11,000. But when the interactions are turned on for the new Card , the [Prior Sales Orders] the
value is (Blank). When the interactions is turned off for the new Card the [Sales Invoices] value is showing all orders
in the Table.

Thank you for your response(s) in advance.

Re: Add Slicer Filter Measure to Non-Slicer Filter Measure

amitchandak — Tue, 14 Mar 2023 03:56:45 GMT

Blevels ,based on what I got

You should use date table and time intelligence for prior values

example

MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD('Date'[Date]))
last MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(dateadd('Date'[Date],-1,MONTH)))
last month Sales = CALCULATE(SUM(Sales[Sales Amount]),previousmonth('Date'[Date]))
MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD('Date'[Date]))
last MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(dateadd('Date'[Date],-1,MONTH)))

Time Intelligence, Part of learn Power BI https://youtu.be/cN8AO3_vmlY?t=27510
Time Intelligence, DATESMTD, DATESQTD, DATESYTD, Week On Week, Week Till Date, Custom Period on Period,
Custom Period till date: https://youtu.be/aU2aKbnHuWs&t=145s

Re: Add Slicer Filter Measure to Non-Slicer Filter Measure

Blevels — Tue, 14 Mar 2023 12:39:24 GMT

Hi,

Thanks for taking the time to respond to my post.

Note, I created a new measure based on the suggested measure [last month Sales]. The return value is (Blank). The value returns (Blank) with and without the Slicer Filter.

This is the formula used:

Measure001 = CALCULATE(

SUM('Open Order (Current) Measures'[OpenOrderLineAmount]),

PREVIOUSMONTH('Date'[Day])

)

Also tried to add the column to the Measure Table and all values returned Blank.

Here, the previous formulas used generates values. The overall problem is not

generating values based on filtering. The problem is with taking a measure that

is dynamically changing with a Slicer and adding it to a measure that is excluded

from the slicer.

Displayed below is the current cards being used:

Re: Add Slicer Filter Measure to Non-Slicer Filter Measure

Blevels — Tue, 21 Mar 2023 13:05:23 GMT

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

Re: Add Slicer Filter Measure to Non-Slicer Filter Measure

Blevels — Wed, 22 Mar 2023 13:55:17 GMT

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)