how to show active dealer or inactive dealer based on sales

[dsmitha]

Hi,

Request your guidance to solve this issue of mine

My table contains sales data for last three months, based on this data we have created a dashboard with multiple visuals. Now my boss wants the matrix visual to have a column which will show whether the dealer is active or inactive based on sales, i.e. if there is no sales for last 60 days then it should show as inactive else active.  request your help to overcome this hurdle

regards,

santhosh unnikrishnan

[amitchandak · Accepted solution]

@dsmitha , First create a measure like

Rolling 60 = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date ],MAX('Date'[Date ]),-60,DAY))

then try meausre like

Inactive = countx(values(Table[Dealer]), if (Isblank([Rolling 60), [Dealer], blank()) )

Active = countx(values(Table[Dealer]), if (not(Isblank([Rolling 60)), [Dealer], blank()) )

Works best with separate date table joined to you table and a Separate Dealer table

Customer Retention Part 1:
https://community.powerbi.com/t5/Community-Blog/Customer-Retention-Part-1-Month-on-Month-Retention/b...

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.

[dsmitha]

hi amitchandak,

thank you very much for your help, this one is cool

thanks again

regards,

dsmitha