Evolution of status between month

[SRK_23]

Hi, I try to modelize evolution on status between month (in number and %), but I'm stuck in the modelization and I don't know how to proceed. I turned the problem in all sides and still don't know how to do it.

file here: http://www.sendbox.fr/fd4f13ed9789f920/Evolution_status.pbix

I would like to count for each month, how many distinct entities (entityId) have reached the status "Awaiting SR" (Status 3 in the Status table) during the current month. Meaning entities that were in status rank 1 or 2 during previous month or beginning of the month and reached status rank 3 during the month.

Thanks you so much for your help and time,

[v-lili6-msft]

hi, @SRK_23

After my test, you may try to this way:

Step1:

change the format of Year_Month column from Text to Whole Number for table dm_clientlifecycle_company and table WOW

Step2:

Then try this formula to create a measure

Measure = 
VAR _values =
    CALCULATETABLE (
        VALUES ( dm_clientlifecycle_company[entityId] ),
        FILTER ( ALL ( WoW ), WoW[Year_Month] = MAX ( WoW[Year_Month] ) - 1 ),
        FILTER (
            'Current Status',
            'Current Status'[Rank] = 1
                || 'Current Status'[Rank] = 2
        )
    )
RETURN
    VAR a =
        CALCULATE (
            DISTINCTCOUNT ( dm_clientlifecycle_company[entityId] ),
            FILTER (
                dm_clientlifecycle_company,
                dm_clientlifecycle_company[entityId] IN _values
            ),
            FILTER ( 'Current Status', 'Current Status'[Rank] = 3 )
        )
    RETURN
        a+0

And use date field from table WOW not use date field in data table.

Result:

here is pbix please try it.

https://www.dropbox.com/s/y58dxsv9wpyaats/Evolution%20status.pbix?dl=0

And i find that there are only litter data meets your criteria (Meaning entities that were in status rank 1 or 2 during previous month or beginning of the month and reached status rank 3 during the month.)

If not your case, Please share some simple data and the expected output.  Do mask sensitive data before uploading

Best Regards,

Lin

[SRK_23]

Hi-- thank you for you time ! 

Does the Measure take into account ALL previous months ?

" (Meaning entities that were in status rank 1 or 2 during ANY previous month or beginning of the month and reached status rank 3 during the month.)"

Thank you,

[v-lili6-msft]

hi, @SRK_23

It takes all previous months  for one year but does not include the months across the years,

for example: 201712 is not previous month for 201801.

Please test the result if meet your requirements, If not your case, Please share some simple sample data and the expected output.  Do mask sensitive data before uploading

Best Regards,

Lin

[SRK_23]

Hi Lin, I'm not sure about the solution, I tryed a work-around (graph below yours) using the measure "New on-boarded client (M)" that I created, and I've got very different results. 

We should have new onboarded client each month.

I feel lost now and I dont know which one is good.

http://www.sendbox.fr/6b7f35c01bdb08bc/Evolution_status_(2).pbix

[v-lili6-msft]

hi, @SRK_23

I have tested on your pbix file,

for example:

we can use slicer to filter data to test the result

drag the Year_Month and rank into slicer separately, and drag Year_Month and entityId into table visual at the bottom left。

then filter Year_Month 201802 and 201803 and Commercial Organisation is Institutional Office.

Now for your measure "New on-boarded client (M)" it still has value 3 in 201803,

but in the data, there is no entity that was in status rank 1 or 2 in 201802 or 201803,

if the result should be 0, you could use this way to test your result.

I spent a lot of time testing the results, It seems that there are only a little bit of data fits.

If I miss something, please remind me.

Best Regards,

Lin