Forum Discussion

jvvv's avatar
jvvv
Frequent Visitor
3 years ago
Solved

Rolling average time intelligence

I need help generating two metrics with respect to time. First, an acute ratio (take the dates from today to 7 days less) 02/18 to 02/12, then tomorrow that I take from 02/19 to 02/13 and so on con...
Need Help
  • grantsamborn's avatar
    grantsamborn
    3 years ago

    Hi jvvv 

    I added a simple date table and came up with the following:

     

    RA Last 7 = 
VAR _Curr = SELECTEDVALUE( 'Date'[Date] )
VAR _Result =
    CALCULATE(
        [Average],
        'Date'[Date] <= _Curr
            && 'Date'[Date] >= _Curr - 7
    )
RETURN
    _Result
/******************************************/
RA Prev 21 = 
VAR _Curr = SELECTEDVALUE( 'Date'[Date] )
VAR _Result =
    CALCULATE(
        [Average],
        'Date'[Date] <= _Curr - 8
            && 'Date'[Date] >= _Curr - 28
    )
RETURN
    _Result
/******************************************/
Final Metric = 
    DIVIDE(
        [RA Last 7],
        [RA Prev 21]
    )

     

     

    Let me know if this helps.

    pbix: RollingAverage__test.pbix

     

grantsamborn's avatar
grantsamborn
Solution Sage
3 years ago

Hi jvvv 

I added a simple date table and came up with the following:

 

RA Last 7 = 
VAR _Curr = SELECTEDVALUE( 'Date'[Date] )
VAR _Result =
    CALCULATE(
        [Average],
        'Date'[Date] <= _Curr
            && 'Date'[Date] >= _Curr - 7
    )
RETURN
    _Result
/******************************************/
RA Prev 21 = 
VAR _Curr = SELECTEDVALUE( 'Date'[Date] )
VAR _Result =
    CALCULATE(
        [Average],
        'Date'[Date] <= _Curr - 8
            && 'Date'[Date] >= _Curr - 28
    )
RETURN
    _Result
/******************************************/
Final Metric = 
    DIVIDE(
        [RA Last 7],
        [RA Prev 21]
    )

 

 

Let me know if this helps.

pbix: RollingAverage__test.pbix

 

jvvv's avatar
jvvv
Frequent Visitor
3 years ago

It works fine. Only I need to change

Average = SUM('Distances'[Distance]
 
Thanks