Forum Discussion

VictorR's avatar
VictorR
Frequent Visitor
7 years ago
Solved

Rolling average with varying conditions

Hi   I have some sample data as I cannot share my actual data:   A table of visits by 6x different people from 3x different companies with their performance.   I am trying to display a visual s...
Need Help
  • v-frfei-msft's avatar
    v-frfei-msft
    7 years ago

    Hi VictorR,

     

    Please check the following steps.

     

    1. Create a dimtime table and create relationship between fact table and date table.

    dimtime = CALENDARAUTO()

    2. Create the measures as below.

     

    latest performance = var maxdate = CALCULATE(MAX(Table1[Date]),ALLSELECTED(Table1))
return 
CALCULATE(MAX(Table1[Performance]),FILTER(Table1,Table1[Date]=maxdate))

    average performance = AVERAGEX(
TOPN(6,Table1,Table1[Date],ASC),Table1[Performance])
    Company's Average performance = CALCULATE(SUM(Table1[Performance]),DATEADD(dimtime[Date],-3,MONTH))/CALCULATE(COUNTROWS(Table1),DATEADD(dimtime[Date],-3,MONTH))

     

    For more details, please check the pbix as attached.

     

    Regards,

    Frank

v-frfei-msft's avatar
v-frfei-msft
Icon for Community Support rankCommunity Support
7 years ago

Hi VictorR,

 

Please check the following steps.

 

1. Create a dimtime table and create relationship between fact table and date table.

dimtime = CALENDARAUTO()

2. Create the measures as below.

 

latest performance = var maxdate = CALCULATE(MAX(Table1[Date]),ALLSELECTED(Table1))
return 
CALCULATE(MAX(Table1[Performance]),FILTER(Table1,Table1[Date]=maxdate))

average performance = AVERAGEX(
TOPN(6,Table1,Table1[Date],ASC),Table1[Performance])
Company's Average performance = CALCULATE(SUM(Table1[Performance]),DATEADD(dimtime[Date],-3,MONTH))/CALCULATE(COUNTROWS(Table1),DATEADD(dimtime[Date],-3,MONTH))

 

For more details, please check the pbix as attached.

 

Regards,

Frank

  • VictorR's avatar
    VictorR
    Frequent Visitor
    7 years ago

    Hi Frank,

     

    How are you narrowing down the Average in your Company's Average Performance measure by Company?

     

    Also apologies I cannot open your .pbix file due to version differences.

     

    My company's IT have set us all up on version: 2.56.5023.1021

      • v-frfei-msft's avatar
        v-frfei-msft
        Icon for Community Support rankCommunity Support
        7 years ago

        Hi VictorR,

         

        Does that make sense? If so, kindly mark my answer as a solution to close the case.

         

        Regards,
        Frank