TRICKY - Average last 6,5,4 and 3 months

[Anonymous]

Hi Experts

See image of the formula in excel where i am trying to calculate the average over the last 6,5,4 and 3 month. I need to re create the average formula in Power BI Based..

The average calculation must ALWAYS starts at the bottom of the green cell as shown in the formula below,,

Sample PBIX with Data

https://www.dropbox.com/s/bko95z5uckgj9j3/sample%283%29.pbix?dl=0 

[mahoneypat]

Thank you for providing a pbix.  What are the values in columns 0-6?  Are those calculated from the Amount column too?  If so, those you should also be done in a measure.  If not, the data should likely be unpivoted to make your analysis/visualization easier (dynamically switching to a different column in a measure would be a challenge too).

Pat

[Anonymous]

Hi Mayoneypat

The values are show in the image below and how month 5 is calculated too...and the amount are to be calculated from the column. I would ideally like a measure... please for each column.

[mahoneypat]

I was trying to understand more about how you generated columns 0-6.  I suspect you should model your data differently to make your analysis differently (or just calculate everything with measures).  For example, this is a measure to get the last 6 months value for the 0 column.

Last 6 Mos for 0 Column =
VAR maxdate =
    MAX ( 'Sample Data'[Date] )
VAR priordate =
    EDATE ( maxdate, -5 )
RETURN
    CALCULATE (
        AVERAGE ( 'Sample Data'[0] ),
        FILTER (
            ALL ( 'Sample Data'[Month], 'Sample Data'[Date] ),
            'Sample Data'[Date] >= priordate
                && 'Sample Data'[Date] <= maxdate
        )
    )

With your current data model, you will need a separate measure for every combination of column (0-6) and months back.  It would be better to unpivot those columns (0-6) and maybe add a disconnected table with values 0-6 and have a single measure that gets all your desired values.  But I don't fully understand your scenario, of course.

Pat