Selected stats vs Average stats

[DaleWatkins]

Hi,

I'm using a gauge to display selected stats (be it year, month or week day) vs the average of the same date range from the previous years and current year inclusive:

I.e. if October 2020 is selected, the red highlighted part will show the stats for October 2020 while the yellow highlighted parts will show the average of October 2018, 19 and 20. The way it works now is if I highlight October 2020 it only shows the averages for October 2020.

Is it possible to do what I'm trying to achieve and if so, how?

Thanks in advance.

[Ashish_Mathur]

Hi,

Something like this will work

Measure for same month last year = calculate([measure],sameperiodlastyear(calendar[date])

Measure for same month last to last year = calculate([Measure for same month last year],sameperiodlastyear(calendar[date])

Total = [measure]+[Measure for same month last year]+[Measure for same month last to last year]

Hope this helps.

[v-alq-msft · Accepted solution]

Hi, @DaleWatkins 

Based on your description, I created data to reproduce your scenario.The pbix file is attached in the end.

Table:

You may create a measure as below.

Avg = 
var _year = SELECTEDVALUE('Table'[Date].[Year])
var _month = SELECTEDVALUE('Table'[Date].[MonthNo])
return
IF(
    HASONEVALUE('Table'[Date].[Year])&&HASONEVALUE('Table'[Date].[Month]),
    CALCULATE(
        AVERAGE('Table'[Value]),
        FILTER(
            ALL('Table'),
            YEAR([Date])>=_year-2&&
            YEAR([Date])<=_year&&
            MONTH([Date])=_month
        )
    ),
    0
)

Result:

Best Regards

Allan

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[DaleWatkins]

@amitchandak

Essentially what I'm trying to achieve is for the gauge to show if the selected data is above the target data, which is an average of the selected data over the entirety of the db.

How I've got my gauge set up is:

Min is 60% average of 2018,19 and 20.

Max is 120% average of 2018, 19 and 20.

Target is 80% average of 2018, 19 and 20.

But this changes and gives me an avg of say one month if only October is selected.

What it should show is:

Red highlight = 237

Min = 126 not the 142 the 2nd pic shows.

Max = 252, not the 284 the 2nd pic shows.

Target = 167, not the 190 the 2nd pic shows.

Hopefully this clears up what I'm trying to achieve. 

[amitchandak]

@DaleWatkins , not very clear. but you can show a value as target value and Min-max values.