TRICKY - Rolling 12 Months

[Anonymous]

Hi Experts

See attached PBIX file with sample data and correct Avg 6mth calculation. I want to amend the current calculated column measure in the attached file so the calculated column measure starts 3 months after the first date in the date column. 

So we should not see any values in the Avg 12 month column until 01 April 2019 

Sample data

https://www.dropbox.com/s/j212m9xy9fzxdrv/FADSampleData%20%282%29.pbix?dl=0  

[amitchandak]

@Anonymous , Try a new column like

Avg 6 month = var _min = eomonth(min([date]),2) return Averagex(SUMMARIZE(filter(Llo_FAD_Data, [Rank] >= earlier([Rank]) -6 && [Rank] <= earlier([Rank]) && [Result] <> 0 && [date] >_min ),[Result] ),[Result])

[Anonymous]

Hi Amit - juts test the new measure an i still get data for period Jan and Feb and AMr 2019

[v-deddai1-msft · Accepted solution]

Hi @Anonymous ,

Use the following calculated column:

Column =
AVERAGEX (
    FILTER (
        SUMMARIZE (
            Llo_FAD_Data,
            Llo_FAD_Data[Year_month],
            Llo_FAD_Data[Rank],
            "_RESULT", SUM ( Llo_FAD_Data[Result] )
        ),
        Llo_FAD_Data[Rank]
            >= EARLIEST ( Llo_FAD_Data[Rank] ) - 6
            && Llo_FAD_Data[Rank] <= EARLIEST ( Llo_FAD_Data[Rank] )
    ),
    CALCULATE ( SUM ( Llo_FAD_Data[Result] ) )
)

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

Best Regards,

Dedmon Dai

[Anonymous]

Hi Dedmon , i used a measure you provided and answer to but i cannot get the measure to give the right result, here is the question using your measure..

https://community.powerbi.com/t5/Desktop/Measure-error-on-rolling-12-month-calculated-column/m-p/199...

[Anonymous]

Thanks Amit