Need help plotting a continuous 7 days of data vs average

[Jansco]

Hi.  I need help plotting a continuous 7 days of data vs average.  Here is how my data is set up.  In Power BI I only have the Date and Sales fields.  I need to create a column with a 7 day total for the 7 days prior to the date in the row.  I also need to calculate the 30 day average from the day after the selected date. And I need to a column for the difference between the 7 day total and the 30 day average, and THAT is what I need to plot in a line graph.  Please tell me this is possible. I need some help on how to achieve this.

[CheenuSing · Accepted solution]

Hi @Jansco ,

I am assuming you have table visual with Date,Sales,7DaysPrior,30DaysAvg ....

Try the following measures

1.  7DaysSalesPrior =
VAR _curDate =
    SELECTEDVALUE ( Sales7Days[Date] )
RETURN
    CALCULATE (
        SUM ( Sales7Days[Sales] ),
        FILTER (
            ALL ( Sales7Days ),
            DATEDIFF ( Sales7Days[Date], _curDate, DAY ) >= 1
                && DATEDIFF ( Sales7Days[Date], _curDate, DAY ) < 8
        )
    )

2.  30DaysSalesAVG =
VAR _curDate =
    SELECTEDVALUE ( Sales7Days[Date] )
RETURN
    CALCULATE (
        AVERAGE ( Sales7Days[Sales] ),
        FILTER (
            ALL ( Sales7Days ),
            DATEDIFF ( Sales7Days[Date], _curDate, DAY ) >= 1
                && DATEDIFF ( Sales7Days[Date], _curDate, DAY ) < 31
        )
    )

3. 7DaysVs30DaysAvg =  [7DaysSalesPrior] - [30DaysSalesAVG]

Replace Sales7Days with your table name.

Cheers

CheenuSing