Compare Initial Status to Current Status

[Anonymous]

Hello, new user, hope I am posting this in the correct spot. my apologies if not.

I have a column with initial status of customers at the start of a new program.

I created a measure to count the number of instances of each possible status.  Such as: Converted, Eligible, Stopped.

This works as expected, I have the status types and the number of each. (Please, ignore the actual status, there are a few to clean up)

I did the same thing for Current Status, and that worked too.

Now, I need to compute the difference in number at the initial start of the program to what the current number is.

So, for example, looking to get -188 as the difference in the number of Converted, and so on.

Was hoping that a new measure subtracting the measure for Initial from the Current would do the trick.

But, it does not.

Any possible solutions would be much appreciated.

Thanks.

[parry2k]

@Anonymous seems like the solution I provided is working. You should accept the actual reply as a solution so that others can easily find it.

✨ Follow us on LinkedIn and  to our YouTube channel

I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make effort to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

⚡ Visit us at https://perytus.com, your one-stop shop for Power BI-related projects/training/consultancy.

[parry2k]

@Anonymous all the best. Keep posted.

✨ Follow us on LinkedIn and  to our YouTube channel

I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make effort to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

⚡ Visit us at https://perytus.com, your one-stop shop for Power BI-related projects/training/consultancy.

[Anonymous]

OK, I think I understand.  I will give it a go.  Thanks again!

[parry2k · Accepted solution]

@Anonymous ah! I see the issue, so you have two status columns in your table one for initial and one for final. This is what you need to do to make it work. This is all about modeling and a bit of DAX.

- Create another table called Status and this table will contain a unique list of Status values from both the Initial and Final status column

- Set the relationship of this table with your data table on the status column. There will be two relationship, one will be status table status column with initial status column (it will be active relationship) and it will be one to many

- Create another relationship between the status column from the status table and the final status column, it will be an inactive relationship and it will be one to many. 

Once all the above is done, add following DAX measures

Initial Status Count = COUNTROWS ( YourDataTable )

Final Status Count = 
CALCULATE ( 
      [Initial Status Count], 
      USERELATIONSHIP ( StatusTable[Status Column], YourDataTable[Final Status Column] )
)

Delta Initial and Final Status Count = [Initial Status Count] - [Final Status Count]

To visualize, use Status column from the new Status table and the above measures and you will have the solution.

✨ Follow us on LinkedIn and  to our YouTube channel

I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make effort to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

⚡ Visit us at https://perytus.com, your one-stop shop for Power BI-related projects/training/consultancy.

[Anonymous]

Hi, thanks for the response.  No, no errors.  Just shows zeros for each category for the variance measure.

[parry2k]

@Anonymous it should work, are you getting any error or what?

✨ Follow us on LinkedIn and  to our YouTube channel

I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make effort to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

⚡ Visit us at https://perytus.com, your one-stop shop for Power BI-related projects/training/consultancy.