Using Moving average by date and by age to calculate another parameter

[Alegarh24]

Hello all!

I'm not a professional data analyst but I have to analyze the performance of objects we develop pretty often. 

I'm very new in Power BI (just finish online training). I need to analyze a big amount of data. I have 400 objects and daily data (20 parameters) for each one for 1.5 years. Excel is not capable to handle this amount. The problem is that I have to calculate one parameter based on another and use its moving average to calculate the 3rd parameter again for every day. I spent already a few days trying to solve the issue but no luck. It is so simple in Excel and so difficult in Power BI.

See the simplified example below:

I have source data like the left table below. Right one what I need to get.

For each date, I have many KPIs for each object. I kept only 2 for simplicity.

I have to:

1. sort the table by the Object and by Date.
2. Calculate for each date and object Efficiency based on Power and Output (Efficiency=[Output]/8/[Power])
3. Calculate Efficiency 8 days moving average.  (Efficiency Mov Avg 😎
4. Using Efficiency Moving average Calculate Heat. ( Heat = (4 - 2.1 * [Efficiency Mov Avg 8]) * [Power] )

SourceRequired

I found how to calculate Measure (below) but I couldn't use it to calculate daily data for Heat

Eff 8 days M =
calculate (
AVERAGEX(L6,L6[Efficiency]),
DATESINPERIOD(
L6[Date],
LASTDATE(L6[Date]),
-8,DAY
)
)

I also found an example of how to create a column with moving average using index but it was for one Object and if I have many than calculations should be separate for each one

P.S. I wanted to attach excel file but there is no such option.

Also, I was wondering that Moving average Measure calculated for dates didn't work (I plotted on graph) when I wanted to plot it with respect to age calculated for every day based on the date of Object was started

I'd appreciate any help

[v-alq-msft · Accepted solution]

Hi, @Alegarh24 

Based on your description, I created data to reproduce your scenario. I calculated moving average three days for test. You may modify 3 days as 8 days in your measure. The pbix file is attached in the end.

Table:

You may create a calculated column and measures as below.

Calculated column:

Efficiency = [Output]/8/[Power]

Measure:

Efficiency Mov Avg 3 = 
CALCULATE(
    AVERAGE('Table'[Efficiency]),
    FILTER(
        ALL('Table'),
        'Table'[Object] = SELECTEDVALUE('Table'[Object])&&
        'Table'[Date] <= SELECTEDVALUE('Table'[Date])&&
        'Table'[Date] >= SELECTEDVALUE('Table'[Date])-2
    )
)

Heat = (4-2.1*[Efficiency Mov Avg 3])*SELECTEDVALUE('Table'[Power])

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.

[Alegarh24]

Thanks a lot! Works perfect