Count of In-Active customers' measure

[Anonymous]

I have our distributors and their chemist sales data and want to count how many chemist were in-active in a given range (in months). For illustration I have attached the sample data and pbix file. 

Please create a measue which gives the total no. of chemists (to be placed in card visual) where no sale was made (in-Active) within 3, 6, 9 or 12 months.

The period duration is coming through a measure as you can also see from the pbix file.

https://www.dropbox.com/sh/oskyjt71nwnizj8/AABCD5H_j1VHrugPia3MKsSla?dl=0

Regards,

[Mariusz · Accepted solution]

Hi @Anonymous 

Try the below measure or see the attached file.

in-active count = 
VAR _period = SELECTEDVALUE( 'Period (in months)'[Period (in months)] )
VAR _end = TODAY()
VAR _start = DATE( YEAR( _end ), MONTH( _end ) - _period, DAY( _end ) )
VAR _active =
    CALCULATE( 
        DISTINCTCOUNT( Daily_Sales[Cust_Code] ),
        TREATAS(
            CALENDAR( _start, _end ),
            'Calendar'[Date]
        ),
        Daily_Sales[Value] > 0 -- remove if you want to include returns
    )
RETURN DISTINCTCOUNT( Daily_Sales[Cust_Code] ) - _active

Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

Please feel free to connect with me.