Forum Discussion

GQ00's avatar
GQ00
Helper III
6 months ago
Solved

Dynamic Filtering based on date selection

Hi,   I want to allow users to filter for customers based on acquistion date. The report has a date table, Dim customer and Fact Sales. Relationships are 1 to * Dim Customers> Fact Sales Dim Date...
Need Help
  • OwenAuger's avatar
    OwenAuger
    6 months ago

    Thanks for sharing the files, and yes unfortunately direct file attachments are not available to all users. Last I checked it was Super Users only but that may have changed 😞

     

    From what I can see, all that potentially needs to be fixed is the definition of which Acquisition Dates to include relative to the "min date" (whether fixed or relative). I may have confused the definition with my earlier suggestion.

     

    Should the Acquisition Date be at least 1 year earlier than the "min date"?

    If so, these calculation item definitions seem to give your expected result.

    I've used EDATE as well since it adds/subtracts a specificed number of months regardless of days per month.

    /*
===========================================================================
  Cust Dynamic
===========================================================================
*/
VAR MinDate = MIN ( 'DATE'[ActualDate] )
VAR CompDynamic =
    CALCULATE (
        SELECTEDMEASURE ( ),
        KEEPFILTERS (
            CUSTOMERS[AcquisitionDate] <= EDATE ( MinDate, -12 )
                && CUSTOMERS[Status] = "Active"
        )
    )
RETURN
    CompDynamic

/*
===========================================================================
  Cust Fixed
===========================================================================
*/
VAR MinDate =
    CALCULATE (
        MIN ( 'DATE'[ActualDate] ),
        ALLSELECTED ( 'DATE' )
    )
VAR CompFixed =
    CALCULATE (
        SELECTEDMEASURE ( ),
        KEEPFILTERS (
            CUSTOMERS[AcquisitionDate] <= EDATE ( MinDate, -12 )
                && CUSTOMERS[Status] = "Active"
        )
    )
RETURN
    CompFixed

    If anything, the condition on CUSTOMERS[AcquisitionDate] should be the only thing to adjust.

     

    Also, if it is good enough to check if a customer exists in SALES, I would also suggest adjusting Cust_CountSales as follows:

    Cust_CounSales =
COUNTROWS (
    SUMMARIZE ( SALES, CUSTOMERS[CustomerID] )
)

    Is this closer to a solution?

OwenAuger's avatar
OwenAuger
Super User
6 months ago

Thanks for sharing the files, and yes unfortunately direct file attachments are not available to all users. Last I checked it was Super Users only but that may have changed 😞

 

From what I can see, all that potentially needs to be fixed is the definition of which Acquisition Dates to include relative to the "min date" (whether fixed or relative). I may have confused the definition with my earlier suggestion.

 

Should the Acquisition Date be at least 1 year earlier than the "min date"?

If so, these calculation item definitions seem to give your expected result.

I've used EDATE as well since it adds/subtracts a specificed number of months regardless of days per month.

/*
===========================================================================
  Cust Dynamic
===========================================================================
*/
VAR MinDate = MIN ( 'DATE'[ActualDate] )
VAR CompDynamic =
    CALCULATE (
        SELECTEDMEASURE ( ),
        KEEPFILTERS (
            CUSTOMERS[AcquisitionDate] <= EDATE ( MinDate, -12 )
                && CUSTOMERS[Status] = "Active"
        )
    )
RETURN
    CompDynamic

/*
===========================================================================
  Cust Fixed
===========================================================================
*/
VAR MinDate =
    CALCULATE (
        MIN ( 'DATE'[ActualDate] ),
        ALLSELECTED ( 'DATE' )
    )
VAR CompFixed =
    CALCULATE (
        SELECTEDMEASURE ( ),
        KEEPFILTERS (
            CUSTOMERS[AcquisitionDate] <= EDATE ( MinDate, -12 )
                && CUSTOMERS[Status] = "Active"
        )
    )
RETURN
    CompFixed

If anything, the condition on CUSTOMERS[AcquisitionDate] should be the only thing to adjust.

 

Also, if it is good enough to check if a customer exists in SALES, I would also suggest adjusting Cust_CountSales as follows:

Cust_CounSales =
COUNTROWS (
    SUMMARIZE ( SALES, CUSTOMERS[CustomerID] )
)

Is this closer to a solution?

GQ00's avatar
GQ00
Helper III
6 months ago

Thanks for your patience, Owen. You are a superstar, this works like a charm.

 

So all we I missed was using all selected on the entire table, like you initially suggested. That was producing blanks at first, hence why I changed it to column; but I may have had something else affecting the output.

 

I really need to familiarise myself more with the difference between selecting the table or column in all selected; unsure why if the slicer relies on actual date, all select on actual date didn't work.

 

Regardless, thank you for your time, patience, and thoroughness, you indirectly enabled a small business to be more data savvy.

 

Have a great day!

 

EDIT: also appreciate your feedback reg customer count, due to other reports, I can't summarise the sales table by customers ID, since it also includes # of customers by product and the like, but I can appreciate why your approach - whenever possible - is preferred, thanks.