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
Highlighted
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
1 REPLY 1
Highlighted
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

Helpful resources

Announcements
Can You Solve These Challenge

Challenge: Can You Solve These?

Find out how to participate in the first Power BI 'Can You Solve These?' challenge.

Community News & Announcements

Community News & Announcements

Get your latest community news and announcements.

Virtual Launch Event

Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

Community Kudopalooza

Win Power BI Swag with Community Kudopalooza!

Each week, complete activities and be qualified in the drawing for cool Power BI Swag.

Users Online
Currently online: 224 members 2,315 guests
Please welcome our newest community members: