Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Blevels
Frequent Visitor

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(
            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.
1 ACCEPTED SOLUTION
Blevels
Frequent Visitor

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)

View solution in original post

4 REPLIES 4
Blevels
Frequent Visitor

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
Frequent Visitor

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

amitchandak
Super User
Super User

@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

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:
Blevels_0-1678797290810.png

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors