Comparing KPIs from two different point in times on a scorecard

[LeeI]

I have a table that is the KPI scores for different agents. The table is updated monthly with the previous month KPIs score for each agent. Below is an example 

Agent Name	as of date	KPI 1	KPI 2	KPI 3
Agent1	3/31/2024	5	4	3
Agent2	3/31/2024	4	3	6
Agent1	4/31/2024	3	3	4
Agent2	4/31/2024	2	5	5
Agent1	5/31/2024	5	3	2
Agent2	5/31/204	4	4	1

I want to be able to create a visual that will allow the end user to select a base date, and a compare date in two seperate slicers. The base date would pull up the agents score for each individual KPI, and the compare date would pull up the same KPI scores, just at a different point in time. Its easy enough to create two seperate matrix's that I could align next to one another, but I am really hoping that I could get everything on one visual. For example, this is what I would like the visual to eventually look like. The end user would be able to select which agent they want to look at, and also pull in any date they wish as the base, and any date they wish as the compare. 

Agent1	Base	Compare
KPI 1	5	3
KPI 2	4	3

Is there any way for me to get this kind of functionality into one visual? Or is my only option to use two seperate matrixs, and just put them side by side on the visual? I would also if possible like to add conditional formatting that would show the delta between each individual KPIs base date and compare date. For example, if agent 1 is selected and the base is March, and the compare is April, the delta for KPI 1 would be -2. If May was used as the compare date and march was still selected as the base date, the delta would be 0

[ValtteriN · Accepted solution]

Hi,

Here is one way to do this:

1. Unpivot your table:

2. add calendar and visual calendar tables. Visual calendar is just a copy of calendar.

3. Dax:

comparison measure = var _date = MAX('visual calendar'[Date]) RETURN

CALCULATE(SUM('Table (39)'[Value]),'Table (39)'[as of date ]=_date)

Base measure = var _date = MAX('Calendar'[Date]) RETURN

CALCULATE(SUM('Table (39)'[Value]),'Table (39)'[as of date ]=_date)

End result:

Here the left slicer is coming from calendar and right one from visual calendar.


I hope this post helps to solve your issue and if it does consider accepting it as a solution and giving the post a thumbs up!

My LinkedIn: https://www.linkedin.com/in/n%C3%A4ttiahov-00001/

[ValtteriN · Accepted solution]

Hi,

Here is one way to do this:

1. Unpivot your table:

2. add calendar and visual calendar tables. Visual calendar is just a copy of calendar.

3. Dax:

comparison measure = var _date = MAX('visual calendar'[Date]) RETURN

CALCULATE(SUM('Table (39)'[Value]),'Table (39)'[as of date ]=_date)

Base measure = var _date = MAX('Calendar'[Date]) RETURN

CALCULATE(SUM('Table (39)'[Value]),'Table (39)'[as of date ]=_date)

End result:

Here the left slicer is coming from calendar and right one from visual calendar.


I hope this post helps to solve your issue and if it does consider accepting it as a solution and giving the post a thumbs up!

My LinkedIn: https://www.linkedin.com/in/n%C3%A4ttiahov-00001/

[LeeI · Accepted solution]

Thanks for taking the time to answer this question @ValtteriN. I was able to figure out what I needed using a slightly different approach. I did create a copy of my date dim table, and called it date dim compare. Then, I set up the cardility between the base date dim table, and the compare date dim table. The cardinality that I used was a one to one cardinality on the date field in the base dim table, to the date field in the comparison date field, and then I set the cross filter direction to both. below is the screenshot of what that relationship looked like. 

After I had this cardinality established, I was able to write dax measures that pulled in the KPIs that I was looking for. My data source actually already aggregates all the scores to a "final aggregation" before I bring the data into PBI. So I did not need to use a sum argument. Additionally, Since I have a date slicer on the dashboard, I actually did not have to incorporate any time arguments within my dax function. I just needed to write a dax measure for the base, and a dax measure for the compare. Here is an example of what worked for me in terms of the dax functions. 

Base dax: 

Cumulative Rank =

Calculate(

MAX(Sales_table_FACT[KPI Score]),

Sales_table_FACT[KPI] = "Cumulative Rank"

)

Compare dax: 

Cumulative Rank Compare =

Calculate(

[Cumulative Rank],

All(Base_Date_DIM),

USERELATIONSHIP(Base_Date_DIM[Date],Compare_Date_DIM[Comparrison Date])

)

When I bring both of these measures into a matrix, I am able to change the base column, and the compare column independently. Here is an example of what my final output looks like 

I appreciate you taking the time to answer my question, and I did incorporate some of your logic into the logic that worked out for me.