Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

TRICKY - Rolling 12 Months

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  

Need Help
  • v-deddai1-msft's avatar
    v-deddai1-msft
    5 years ago

    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

5 Replies

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    5 years ago

    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's avatar
      Anonymous
      Not applicable
      5 years ago

      Thanks Amit

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

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

      • v-deddai1-msft's avatar
        v-deddai1-msft
        Icon for Community Support rankCommunity Support
        5 years ago

        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