switch function help

[Anonymous]

I have a group of vendors who give sales every month, and we have so many I want to keep track of who has lapsed one month, 2 month, 3 months, meaning - hasn't provided any sales. I have a name field of each provider and I also have a field that has the amount of sales which is "##". 

So below you can see that I am trying to say if there were 0 sales last month, that is a "1 month lapse" ...and so on. 

This is in a measure, not sure if it is better to make this into a column assigning these "1 month lapse",etc to rows with the provider name. 

Any help appreciated!

Switch ( True(),
CALCULATE ( counta ( LNApps_ProvDim[Provider_Name] ), 'LNApps_Facts'[##] =0,DATEADD('LNApps_AppDateDim'[ActualDate].[Date], -1, MONTH)),"1 Month Lapse"  ,
    CALCULATE ( counta( LNApps_ProvDim[Provider_Name] ), 'LNApps_Facts'[##] =0,DATEADD('LNApps_AppDateDim'[ActualDate].[Date], -2, MONTH)),"2 Month Lapse")

[v-sihou-msft · Accepted solution]

@Anonymous

Based on your description, you want to calculte the consecutive "0 sales" for each vendor group. Right?

I assume you have month level analysis data for each vendor group. Then you can add a flag column to tag if current month has 0 sales. Then create a measure to get the consecutive "0 sales" months. Please refer to a thread below with similar "consecutive wins" scenario:

http://community.powerbi.com/t5/Desktop/Count-last-actual-consecutive-sorted-rows/td-p/149683

Regards,

[Anonymous]

Currently this is how I do it in excel for example

January

1 month lapse                2 Month Lapse               3 Month Lapse              4 Month Lapse          5 Month Lapse     ............. Active

1                                        

                                                                                   1

                                                                                                                                 1

                                                                                                                                                                                                  1

1 

                                                    1

A one indicates the vendor has lapsed for that column period.