Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Filtering based on filtered values

Hi All,   I am stuck in a problem. Here are the details. I have a sales data which has sales with customer name and order date fields. I need to count customer which were active 4 months ago but ar...
Need Help
  • Anonymous's avatar
    Anonymous
    5 years ago

    Hi Anonymous ,

    You can create a calculated column as below:

    Active customer = 
VAR _maxorddate =
    CALCULATE ( MAX ( 'Table'[Date] ), ALL ( 'Table' ) )
RETURN
    CALCULATE (
        MAX ( 'Table'[Customer] ),
        FILTER (
            SUMMARIZE (
                'Table',
                'Table'[Customer],
                "ConL", CALCULATE ( MAX ( 'Table'[Date] ), ALLEXCEPT ( 'Table', 'Table'[Customer] ) )
            ),
            DATEDIFF ( [ConL], _maxorddate, MONTH ) < 4
        )
    )

    If the above one is not applicable for your scenario, please provide your expected result with more explanation. Thank you.

    Best Regards

amitchandak's avatar
amitchandak
Super User
5 years ago

Anonymous , With the help from date table and time intelligence . Create measures like

 

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

 

Not active now = countx(values(Table[Customer]), if(isblank([MTD Sales]) && not(isblank(last MTD Sales)),Table[Customer], blank()))

 

To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :radacad sqlbi My Video Series Appreciate your Kudos.