Reply
Highlighted
Frequent Visitor
Posts: 14
Registered: ‎08-07-2018
Accepted Solution

Compare the last 30 days to the last 365 days

[ Edited ]

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.


Accepted Solutions
Frequent Visitor
Posts: 14
Registered: ‎08-07-2018

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


All Replies
Frequent Visitor
Posts: 14
Registered: ‎08-07-2018

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