New column that averages the values of the last 14 days

[JC_Silva]

I have a table in Power BI where I need to add a new column (it can't be a measure) that calculates per row, what is the average number of cases from the previous 14 days. Here is an extract of the dataset with the expected value ("14-day average"):

Date	County	Fips	Cases	14-day average
4/10/2020	Duval	12031	23	37.07
4/11/2020	Duval	12031	21	37.07
4/12/2020	Duval	12031	29	37.57
4/13/2020	Duval	12031	23	37.71
4/14/2020	Duval	12031	27	36.50
4/15/2020	Duval	12031	28	36.21
4/16/2020	Duval	12031	30	35.00
4/17/2020	Duval	12031	41	35.29
4/18/2020	Duval	12031	18	32.00
4/19/2020	Duval	12031	19	31.50
4/20/2020	Duval	12031	42	29.86
4/21/2020	Duval	12031	13	27.71
4/22/2020	Duval	12031	16	26.29
4/23/2020	Duval	12031	31	25.79
4/24/2020	Duval	12031	0	24.14
4/25/2020	Duval	12031	10	23.36
4/26/2020	Duval	12031	19	22.64
4/27/2020	Duval	12031	6	21.43
4/28/2020	Duval	12031	11	20.29
4/29/2020	Duval	12031	6	18.71
4/30/2020	Duval	12031	5	16.93
5/1/2020	Duval	12031	13	14.93
5/2/2020	Duval	12031	13	14.57
5/3/2020	Duval	12031	7	13.71
4/10/2020	Hillsborough	12057	19	35.86
4/11/2020	Hillsborough	12057	22	35.14
4/12/2020	Hillsborough	12057	61	38.14
4/13/2020	Hillsborough	12057	54	38.64
4/14/2020	Hillsborough	12057	6	36.71
4/15/2020	Hillsborough	12057	51	36.29
4/16/2020	Hillsborough	12057	8	33.86
4/17/2020	Hillsborough	12057	46	32.93
4/18/2020	Hillsborough	12057	19	31.86
4/19/2020	Hillsborough	12057	17	30.29
4/20/2020	Hillsborough	12057	13	28.43
4/21/2020	Hillsborough	12057	6	26.50
4/22/2020	Hillsborough	12057	3	25.07
4/23/2020	Hillsborough	12057	26	25.07
4/24/2020	Hillsborough	12057	14	24.71
4/25/2020	Hillsborough	12057	19	24.50
4/26/2020	Hillsborough	12057	7	20.64
4/27/2020	Hillsborough	12057	14	17.79
4/28/2020	Hillsborough	12057	18	18.64
4/29/2020	Hillsborough	12057	25	16.79
4/30/2020	Hillsborough	12057	19	17.57
5/1/2020	Hillsborough	12057	39	17.07
5/2/2020	Hillsborough	12057	77	21.21
5/3/2020	Hillsborough	12057	41	22.93

The following code seems to work, but when I match it to stand out I start to see more and more discrepancies as I go back in time:

Avg14DíasCasos ?

VAR EarlierDate ? DATEADD(Data[date],-13,DAY )

VAR SumCases á CALCULATE(AVERAGE(Data [NewCases]),FILTER(Data, Data [date]>-EarlierDate), Data [fips] - EARLIER(Data[fips]))

Return

SumCases

Any ideas?

[v-yingjl · Accepted solution]

Hi @JC_Silva ,

Based on your data sample, it seems incomplete so that the calculated column I have created will show the same result after 2020/4/23 in each county:

14-Average day = 
VAR _currentday = 'Table'[Date]
VAR _periodday = _currentday - 13
VAR _county = 'Table'[County]
VAR _14average =
    CALCULATE (
        AVERAGEX ( 'Table', 'Table'[Cases] ),
        FILTER (
            ALL ( 'Table' ),
            [Date] >= _periodday
                && [Date] <= _currentday
                && [County] = _county
        )
    )
RETURN
    _14average

Before 4/23 there are not enough data so it calculate current days average

Best Regards,
Yingjie Li

If this post helps then please consider Accept it as the solution to help the other members find it more quickly.

[parry2k]

@JC_Silva most important is to have date dimension in your model, for time intelligence calculations like this one, as a best practice, one must have a date dimension in their model. There are many posts on the internet on how you can add one. Once you have a date dimension, set a relationship with your data table, date dimension on one side, and data table on many sides of the relationship.

Add the following measure:

Avg 14 days = CALCULATE ( AVERAGE ( DataTable[Column] ), DATEADD( DateDimension[Date], -13 DAY ) )

In a visual, drop date from date dimension and above measure and you will have the result

I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos whoever helped to solve your problem. It is a token of appreciation!