New column that averages past 14 days values

[JC_Silva]

I have a table in Power BI where I need to add a new column (cannot be a measure) that calculates by row, what is the average number of cases for 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 appears to work but when I match it up to excel I start to see more and more discrepancies as I go back in time:

Avg14DaysCases =

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 thoughts?

[edhans · Accepted solution]

This will average the last 14 days. (Actually, will average up to the last 14 days. If it is on April 15 of your data, there are only 5 days to average in that case)

14 Day Average = 
VAR CurrentDay = [Date]
VAR FirstDay = [Date] - 13
VAR CurrentCounty = [County]
VAR AverageCases = 
    AVERAGEX(
        FILTER(
            ALL('Cases'),
            [County] = CurrentCounty
                && [Date] <= CurrentDay
                && [Date] >= FirstDay
        ),
        [Cases]
    )
RETURN
    AverageCases

[amitchandak · Accepted solution]

@JC_Silva , You can use the rolling formula with Date calendar

Rolling 14 = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD(Table[Date],ENDOFMONTH(Sales[Sales Date]),-14,DAY))
Rolling 14 = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD(Table[Date Filer],MAX(Sales[Sales Date]),-14,DAY))

To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :
https://radacad.com/creating-calendar-table-in-power-bi-using-dax-functions
https://www.archerpoint.com/blog/Posts/creating-date-table-power-bi
https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/

[JC_Silva]

@amitchandak Thank you! Tested too and also works!

[JC_Silva]

@amitchandak Thank you! Tested it out also and it also works!

[edhans · Accepted solution]

This will average the last 14 days. (Actually, will average up to the last 14 days. If it is on April 15 of your data, there are only 5 days to average in that case)

14 Day Average = 
VAR CurrentDay = [Date]
VAR FirstDay = [Date] - 13
VAR CurrentCounty = [County]
VAR AverageCases = 
    AVERAGEX(
        FILTER(
            ALL('Cases'),
            [County] = CurrentCounty
                && [Date] <= CurrentDay
                && [Date] >= FirstDay
        ),
        [Cases]
    )
RETURN
    AverageCases

[JC_Silva]

@edhans Thank you. Tested this morning and it works!

[edhans]

Excellent @JC_Silva . Glad your project is moving forward.