DAX improvement or decline in scores over months

[marchn]

Hello

I'm looking for some help with DAX. I have a table in Power BI which lists organisations, their assigned relationship type (Partner, Engaged, Interested, Neutral, Critic), a calculated score based on the relationship type (e.g. Partner = 5, Engaged =4 etc.) and then dates that the snapshot of data was taken. Therefore, each organisation will appear more than once, because in May they had one relationship type and then it may (or may not) have changed in July and August.

I want to create a visuals which show the number of organisations to have improve, declined or remained static with their relationship types or scores between any two periods chosen by the user. For instance, if an organisation goes from Neutral (2) to Interested (3), then they have improved. If they go from Partner (5) to Engaged (4) then they have declined. I have two slicers based on two calendar tables - the comparison table has an inactive relationship and I've managed to use this on other visuals doing different types of comparisons.

Can anyone help me with the DAX to achieve the above scenarios?

Org ID	Org Name	Relationship Type	Relationship Score	Date
1	Costco	Neutral	2	31/05/2023
2	WH Smith		0	31/05/2023
3	Starbucks	Critic	1	31/05/2023
4	Wilkos	Interested	3	31/05/2023
5	Pizza Hut		0	31/05/2023
6	McDonald's		0	31/05/2023
7	KFC	Critic	1	31/05/2023
8	Boots	Neutral	2	31/05/2023
9	Argos	Critic	1	31/05/2023
1	Costco	Neutral	2	30/06/2023
2	WH Smith	Critic	1	30/06/2023
3	Starbucks	Critic	1	30/06/2023
4	Wilkos	Interested	3	30/06/2023
5	Pizza Hut	Neutral	2	30/06/2023
6	McDonald's	Critic	1	30/06/2023
7	KFC	Critic	1	30/06/2023
8	Boots	Neutral	2	30/06/2023
9	Argos	Critic	1	30/06/2023
1	Costco	Critic	1	30/07/2023
2	WH Smith	Critic	1	30/07/2023
3	Starbucks	Interested	3	30/07/2023
4	Wilkos	Neutral	2	30/07/2023
5	Pizza Hut	Neutral	2	30/07/2023
6	McDonald's	Neutral	2	30/07/2023
7	KFC	Neutral	2	30/07/2023
8	Boots	Interested	3	30/07/2023
9	Argos	Neutral	2	30/07/2023
10	River Island (new org)	Critic	1	30/07/2023
1	Costco	Neutral	2	31/08/2023
2	WH Smith	Neutral	2	31/08/2023
3	Starbucks	Partner	5	31/08/2023
4	Wilkos	Interested	3	31/08/2023
5	Pizza Hut	Interested	3	31/08/2023
6	McDonald's	Engaged	4	31/08/2023
7	KFC	Neutral	2	31/08/2023
8	Boots	Neutral	2	31/08/2023
9	Argos	Interested	3	31/08/2023
10	River Island (new org)	Neutral	2	31/08/2023

Example in Excel of what I'm trying to achieve from the visual - using different data.

[lbendlin]

Here is the first step - a slight cleanup of the data model

Then the next step is to calculate the trend for each org.

Now for your Relationship type change expected outcome you need to indicate over which time interval you want that.  Month by month or cumulative?

[marchn · Accepted solution]

Hi

I've been trying for a couple of days to get the DAX you suggested to work and to try and understand what the DAX is doing (which I'm failing at), but for some reason it is just returning blank results and I'm not sure what I'm doing wrong. Perhaps it's my relationships between tables. I have to have the comparison calendar so that other functionality works. I have pasted what I have done below. Some of the field names are slightly different to yours, but I think I've selected correctly. Any ideas what I might be doing wrong?

Results - blank for delta

My DAX

I've temporarily removed the relaltionship with the inactive comparison calendar just to see if that was the problem:

[lbendlin]

You can use the filter pane to specify any number of months that you like (including two). The formula will work either way (you may have to replace REMOVEFILTERS with ALLSELECTED() ).

[marchn]

Hi

I really appreciate you taking the time to help me. I have replaced REMOVEFILTERS with ALLSELECTED, but the Delta field is still showing up as blank. I've tried creating a completly new query from scratch and using a simplified date table, but that also doesn't work. Any ideas what I might be doing?

[lbendlin]

you might be overcomplicating this

[marchn]

Hello - thank you for attaching your version. I can see it's working in your version. So I recreatd my version to completely mimic yours, but mine still doesn't work. I have no idea why. Perhaps I have a setting wrong. With the DAX, the only thing that is different for me is that I have ' ' around the table names. I'm not sure if this make a difference, or if the source my query is based on caused a difference.  Or if I'm on a different version of PowerBI. When I opened your query, I noticed that it stated the saved query was created in a newer version of PowerBi. 

[lbendlin]

The single quotes are only required if the table name has spaces. 

Can you confirm that your "Date"  table is actually one? Did you mark it as such?

[marchn]

Thank you. It wasn't previously marked as a date table, but I have now marked it as the date table. 

[lbendlin]

See if you can post your sample pbix

https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...

[marchn]

Hi

Here is my link to my PowerBI - hopefully this will work. If you're able to see what I've done wrong, that would be really helpful. The real query I have to get this to work on is larger, with far more data and more complex, but one step at a time I guess. Thank you. 

Practice_Scores2.pbix

[lbendlin]

You used the wrong date column.  Needs to come from the Dates table

[marchn]

Oh my goodness!!!! I can't believe it! that's it! I knew it had to be something simple like that, that I have been missing. Thank you so much for perservering with me. I really appreciate it. 

[marchn]

Apologies for adding more questions - but now I've managed to get this working, is there a way that I can allow my users to select the two months they want to compare? So if they want to compare June with August,  or May with August etc, so the deltas change depending on what the user selects?

[lbendlin]

That is already built-in.  use a slicer to let them choose the months. Educate them that it won't work if they pick less than or more than two values.

[marchn]

Oh yes - I can see I need to press ctrl to select more than one, or uncheck that option in settings. 

Hopefully this is my final step - but now that I have achieved this, I want to be able to count how many organisations have a positive delta (meaning they have improved their score), negativel delta (meaning they have declined) or zero delta (meaning they have stayed static between the two periods selected). I suppose a "countif" approach if it were in Excel, so I can display the counts similar to the below. Thank you so much for your help - I've been totally stumped by this one, but I'm learning so much. 

[lbendlin]

One small advice if I may - forget what you have learned in Excel.  (At least for this task).  Embrace Power BI's capabilities, don't try to make it into something it isn't.

Here's one of many options:

[marchn]

This works perfectly. You're a genius - thank you. I can see I really need to learn how to use variables and the syntax for Power BI - so thank you for your advice. 

I have one other question - perhaps I need to ask it in a new thread. I would like to have a gauge or a KPI which shows the percetage of "improved" organisations between the two selected dates - I have a target of improving x% (let's say 50%) of organisations by 31 March, is there a way of showing the progress towards this target on a gauge or KPI visual? I realise that I would need to select the dates as the period I have to achieve the target. 

[marchn]

Hi thank you so much for taking the time to reply. I will make a start on your first steps you have proposed. 

I would like my comparison to be month-by-month, so the user could choose any two months to compare. This is why I have two calendars in my model, because I have some other visualisations where one slicer is based on "calendar" and the other is based on the inactive "comparison calendar". In my actual dataset I have an organisation list, so I presume I can make the link between org IDs there.