Slicers in Calculations

[alexhameltrox]

I haven't been able to find a solution to this on the forums so I'm hoping that asking a direct questions will help.

The Outline

I have 2 tables I'm looking at:

Accounts - A list of accounts with basic information like the number of employees 

Sales - A table of each line item of sales for the company with information like revenue

I have these two tables being related using an id field and this seems to be working properly.

In the visualization I have a table listing the accounts and the related revenue from the sales table with a slicer for Year/Month of invoice date.

The Problem

I'm trying to make a calculation to see if a customer is active or not based on the sales within the selected dates of the slicer.

For example:

I want to see if a customer was active in the last 12 months based on revenue being higher than 0

I want to see if a customer was active during a quarter last year based on revenue being higher than 0

The issue I'm running into is that when I bring revenue from the sales table directly into the table visualization it works just fine using the slicer to limit the data. However, when I try to use SUM(Revenue) in a calculation to determine if it was active based on the slicer selection it seems to remove all the filters including the relation. I've tried multiple combinations of SUMX and CALCULATE as well as FILTER, ALLSELECTED, ALLEXCEPT and KEEPFILTERS to try and resolve this but have had no success.

Here's my current attempt forumula:

Active Revenue =

VAR Min_Date = MINX(

    ALLSELECTED(Sales),

    MIN(Sales[Invoice Date].[Date])

)
RETURN
CALCULATE(

    SUM(Sales[Revenue]),
    FILTER(
        Sales,

        Sales[customer_id] = Accounts[guid]
        && Sales[Invoice Date].[Date] >= Min_Date
    )
)

The sum for this is properly relating the customer id but is failing to filter the invoice dates based on the slicer. Any ideas on what I'm doing wrong would be greatly appreciated.

[MFelix]

Hi  @alexhameltrox ,

If you want to have the calculation done over the past 12 months you need to make an overal filter to get the information using allselected you are only picking up the dates between the selected values on the slicer, also if you want to have the values that add no sales the sum of the values also needs to be in your filter.

You need to use a summarization to get the a temporary table and then make the calculations on top of that.

Can you please share a mockup data or sample of your PBIX file and expected result. You can use a onedrive, google drive, we transfer or similar link to upload your files.

If the information is sensitive please share it trough private message.