Rolling average time intelligence

[jvvv]

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

Day	Name	Distance
28/12/2022	Manuel	6948,94
29/12/2022	Manuel	5086,62
30/12/2022	Manuel	4219,64
31/12/2022	Manuel	5370,32
01/01/2023	Manuel	5417,95
02/01/2023	Manuel	5023,13
03/01/2023	Manuel	3314,83
04/01/2023	Manuel	3810
05/01/2023	Manuel	3806,19
06/01/2023	Manuel	5468,44
07/01/2023	Manuel	2609,42
08/01/2023	Manuel	5773,7
09/01/2023	Manuel	2638,92
10/01/2023	Manuel	4164
11/01/2023	Manuel	4915,58
12/01/2023	Manuel	1061,04
13/01/2023	Manuel	5468,11
14/01/2023	Manuel	5705,44
15/01/2023	Manuel	5827,5
16/01/2023	Manuel	4497,31
17/01/2023	Manuel	4456,62
18/01/2023	Manuel	5222,84
19/01/2023	Manuel	3702,69
20/01/2023	Manuel	6946,15
21/01/2023	Manuel	4396,6
22/01/2023	Manuel	5819,64
23/01/2023	Manuel	4250,85
24/01/2023	Manuel	3645,42
25/01/2023	Manuel	4123,22
26/01/2023	Manuel	4123,22
27/01/2023	Manuel	5433,86
28/01/2023	Manuel	3843,52
29/01/2023	Manuel	3971,31
30/01/2023	Manuel	6254,94
31/01/2023	Manuel	1327,33
01/02/2023	Manuel	4948,84
02/02/2023	Manuel	1509,6
03/02/2023	Manuel	7682,11
04/02/2023	Manuel	1471,07
05/02/2023	Manuel	4408,24
06/02/2023	Manuel	3496,35
07/02/2023	Manuel	5200,13
08/02/2023	Manuel	7020,01
09/02/2023	Manuel	3314,03
10/02/2023	Manuel	1349,39
11/02/2023	Manuel	2488,96
12/02/2023	Manuel	4837,05
13/02/2023	Manuel	4949,03
14/02/2023	Manuel	2488,96
15/02/2023	Manuel	9786,08
16/02/2023	Manuel	6619,62
17/02/2023	Manuel	6272,91
18/02/2023	Manuel	1691,8
19/02/2023	Manuel	6464,52
20/02/2023	Manuel	880,58

[Ashish_Mathur]

Hi,

You may download my PBI file from here.

Hope this helps.

[jvvv]

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

[grantsamborn · Accepted solution]

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]

It works fine. Only I need to change

Average = SUM('Distances'[Distance]

Thanks