cancel
Showing results for
Did you mean:
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.

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
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```
Highlighted
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```

Announcements

#### Challenge: Can You Solve These?

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

#### Community News & Announcements

Get your latest community news and announcements.

#### 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.

#### Win Power BI Swag with Community Kudopalooza!

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

Top Kudoed Authors
Users Online
Currently online: 224 members 2,315 guests
Recent signins: