cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
jtownsend21 Member
Member

Compare the last 30 days to the last 365 days

I have a KPI which measures the revenue we generate per the number of appeals we settle. What I would like to be able to do is show the variance of a given 30 day period to the average of that kpi over the last 365 days in a tooltip. The picture below shows the average value in 30 day increments. Eg. the value for January is the value for the 30 days prior to today. 

 

Rev per Appeal.PNG

The average line from the analytics pane shows me the average at $3,254 so when I hover over January which is $4,874, I want the tooltip would say 45% increase. 

Currently I have 

 

DIVIDE(
CALCULATE([**Rev/ Settled Appeal**]),
CALCULATE([**Rev/ Settled Appeal**],
'Date'[**L365**] = "L365")
)-1

 

Where 'Date'[**L365**]  returns "L365" if it's in the last 365 days. Unfortunately this is showing me + 8% for the January example instead of +45%. This is leading me to think that the average line is calculating the average in a different way than I was thinking.

1 ACCEPTED SOLUTION

Accepted Solutions
jtownsend21 Member
Member

Re: Compare the last 30 days to the last 365 days

Posting in case it helps someone in the future... I figured it out. I needed an ALL statement for each filter I was using. I thought a single ALL statement on my Date Table would resolve it. Looks like I needed one for each column. 

This got me the Value for the last 365 days: 

 

 
**Rev/ Settled Appeal L365** =
CALCULATE([**Rev/ Settled Appeal**],
ALL('Date'[Date]),
ALL('Date'[**30 Day Increments - Date End**]),
'Date'[**L365**] = "L365"
)
 
 
This got me the variance %: 
 
 
**Rev/ Settled Appeal L365 Variance %** =
DIVIDE(
[**Rev/ Settled Appeal**],
[**Rev/ Settled Appeal L365**]
)-1

View solution in original post

1 REPLY 1
jtownsend21 Member
Member

Re: Compare the last 30 days to the last 365 days

Posting in case it helps someone in the future... I figured it out. I needed an ALL statement for each filter I was using. I thought a single ALL statement on my Date Table would resolve it. Looks like I needed one for each column. 

This got me the Value for the last 365 days: 

 

 
**Rev/ Settled Appeal L365** =
CALCULATE([**Rev/ Settled Appeal**],
ALL('Date'[Date]),
ALL('Date'[**30 Day Increments - Date End**]),
'Date'[**L365**] = "L365"
)
 
 
This got me the variance %: 
 
 
**Rev/ Settled Appeal L365 Variance %** =
DIVIDE(
[**Rev/ Settled Appeal**],
[**Rev/ Settled Appeal L365**]
)-1

View solution in original post

Helpful resources

Announcements
Exclusive LIVE Community Event #3 – Ask Arun Anything

Exclusive LIVE Community Event #3 – Ask Arun Anything

Join us in the third Triple A event!

Meet the 2020 Season 1 Power BI Super Users!

Meet the 2020 Season 1 Power BI Super Users!

It’s the start of a new Super User season! Learn all about the new Super Users and brand-new tiered recognition system.

January 2020 Community Highlights

January 2020 Community Highlights

Make sure you didn't miss any of the things that happened in the community in January!

Top Solution Authors
Top Kudoed Authors