Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
LeeI
Frequent Visitor

Comparing KPIs from two different point in times on a scorecard

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 
Agent13/31/2024543
Agent2 3/31/2024436
Agent1 4/31/2024334
Agent2 4/31/2024255
Agent15/31/2024532
Agent25/31/204441

 

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. 

 

Agent1Base Compare 
KPI 1 53
KPI 2 43

 

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

2 ACCEPTED SOLUTIONS
ValtteriN
Super User
Super User

Hi,

Here is one way to do this:

1. Unpivot your table:

ValtteriN_0-1717844858645.png

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:

ValtteriN_1-1717845555885.png

 

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/





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




View solution in original post

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. 

 

LeeI_0-1718275102051.png

 

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 
 
LeeI_1-1718275596769.png

 

 
 
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. 
 

 

View solution in original post

2 REPLIES 2
ValtteriN
Super User
Super User

Hi,

Here is one way to do this:

1. Unpivot your table:

ValtteriN_0-1717844858645.png

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:

ValtteriN_1-1717845555885.png

 

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/





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




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. 

 

LeeI_0-1718275102051.png

 

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 
 
LeeI_1-1718275596769.png

 

 
 
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. 
 

 

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors