Return specific value from measure

[Pedro_Silveira]

I'm having a bit of an issue with my dashboard

Currently trying to implant a new dashboard in my company (they only used Excel) and one thing my boss requires is the mobile average of a certain period of time. But instead of having different values for different dates, he prefers to see the mobile average of the last date as a straight line over the dashboard.

Easy solution was to use the built-in function on column graphs, but also need a bit more data on the board, making the function unavailable.

Already got mobile average calculated in a measure (MobileAVG), that's my main issue on returning expected value.

[Anonymous · Accepted solution]

Hi @Pedro_Silveira ,

Please try to use the DAX language to create a measure that calculates the mobile average of the last date in your data. You can then add this measure to your line chart or any other visual that supports measures. For example, if you have a table called Sales with columns Date and Amount, you can create a measure like this:

Mobile Average =
VAR _LastDate =
    MAX ( Sales[Date] )
VAR _Period = 30 // change this to your desired period 
VAR _MobileAverage =
    AVERAGEX (
        FILTER ( Sales, Sales[Date] >= _LastDate - _Period && Sales[Date] <= _LastDate ),
        Sales[Amount]
    )
RETURN
    _MobileAverage

This formula uses the MAX function to get the last date in the data, the AVERAGEX function to calculate the average amount over a specified period, and the FILTER function to filter the data by date range. You can then add this measure to your line chart and it will show a straight line with the mobile average value.

How to Get Your Question Answered Quickly 

If it does not help, please provide more details with your desired output and pbix file without privacy information (or some sample data) .

Best Regards
Community Support Team _ Rongtie

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

[amitchandak]

@Pedro_Silveira , Try a measure like

Avg Line =
var _max =maxx(allselected(Date), Date[Date])
return

Calculate(AverageX(values(Table[Mobile]), Calculate(Sum(Table[Value]))), filter(Date, Date[Date] = _max))