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 continuously.
After a chronic ratio, which takes from 7 days less, up to 28 days. (From 02/11 to 01/22) And so continuously, tomorrow I would take from 02/12 to 01/23 and so on continuously.


After the last metric that would be Acute/Chronic (Acute/Chronic Ratio =
DIVIDE([Acute Ratio],[Chronic Ratio]))

 

I can't find a solution to mainly calculate the chronic ratio. I can't find how to tell it in DAX to calculate the 21 days prior to the last 7

 

DayNameDistance
28/12/2022Manuel6948,94
29/12/2022Manuel5086,62
30/12/2022Manuel4219,64
31/12/2022Manuel5370,32
01/01/2023Manuel5417,95
02/01/2023Manuel5023,13
03/01/2023Manuel3314,83
04/01/2023Manuel3810
05/01/2023Manuel3806,19
06/01/2023Manuel5468,44
07/01/2023Manuel2609,42
08/01/2023Manuel5773,7
09/01/2023Manuel2638,92
10/01/2023Manuel4164
11/01/2023Manuel4915,58
12/01/2023Manuel1061,04
13/01/2023Manuel5468,11
14/01/2023Manuel5705,44
15/01/2023Manuel5827,5
16/01/2023Manuel4497,31
17/01/2023Manuel4456,62
18/01/2023Manuel5222,84
19/01/2023Manuel3702,69
20/01/2023Manuel6946,15
21/01/2023Manuel4396,6
22/01/2023Manuel5819,64
23/01/2023Manuel4250,85
24/01/2023Manuel3645,42
25/01/2023Manuel4123,22
26/01/2023Manuel4123,22
27/01/2023Manuel5433,86
28/01/2023Manuel3843,52
29/01/2023Manuel3971,31
30/01/2023Manuel6254,94
31/01/2023Manuel1327,33
01/02/2023Manuel4948,84
02/02/2023Manuel1509,6
03/02/2023Manuel7682,11
04/02/2023Manuel1471,07
05/02/2023Manuel4408,24
06/02/2023Manuel3496,35
07/02/2023Manuel5200,13
08/02/2023Manuel7020,01
09/02/2023Manuel3314,03
10/02/2023Manuel1349,39
11/02/2023Manuel2488,96
12/02/2023Manuel4837,05
13/02/2023Manuel4949,03
14/02/2023Manuel2488,96
15/02/2023Manuel9786,08
16/02/2023Manuel6619,62
17/02/2023Manuel6272,91
18/02/2023Manuel1691,8
19/02/2023Manuel6464,52
20/02/2023Manuel880,58
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

     

4 Replies

  • 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
    • jvvv's avatar
      jvvv
      Frequent Visitor
      3 years ago

      He doesn't calculate it well like that. I have tried, but the calculations are not correct. Thank you very much for the help