Adding a rolling weekly, 14 days and 30 days average

[Anonymous]

Hello Community,

I have multiple DBs from which i am pulling data from. One of them is attached below. I have cards in my dashboards with the sum of Profit, revenue, units sold, promotions revenue, and few more calculated columns for four-time ranges.  Yesterday, last 7 days, last 14 days, last 30 days. It looks like below.

1) Yesterday(4/25/2020)                   2) Last 7 days  (4/19 - 4/25)

Profit                                                     Profit                             

Revenue                                                Revenue

Units                                                     Units

Promotion Revenue,                            Promotion Revenue,

more ...                                                 more ...

3) last 14 days(4/12 - 4/25)                4) Last 30 days (3/25 - 4/25)

Profit                                                      Profit

Revenue                                                 Revenue

Units                                                      Units

Promotion Revenue,                              Promotion Revenue,

more ...                                                  more ...

To get these rolling values I just add a relative dates filters in the filter panel to show the data for the last 1 day, last 7 days, last 14 days, last 30 days.

Now, Revenue, units come from table 1, and rest come from table 2. Both tables have a relationship with the date table.

My objective is to get the format as attached in attached "Output needed file"

Output Needed: https://docs.google.com/document/d/1gII1YB3VYNnYPeBEPCshv4MUNm0Xa7rhHa9c0agIleo/edit?usp=sharing

Data: https://docs.google.com/spreadsheets/d/1P1Tfd66SCOCKtUU_MK-DqeE_KgMjipDlPb6i5P38bAg/edit?usp=sharing

[Anonymous · Accepted solution]

HI @Anonymous,

You can refer to the following blog to use date function manually define filter date range to calculate the rolling results:

Time Intelligence "The Hard Way" (TITHW)  

Yesterday =
CALCULATE (
    AVERAGE ( Table[Sales] ),
    FILTER ( Table, [Date] = TODAY() - 1 )
)

Last week =
CALCULATE (
    AVERAGE ( Table[Sales] ),
    FILTER ( Table, [Date] >= TODAY () - 7 && [Date] < TODAY () )
)

Last 2 week =
CALCULATE (
    AVERAGE ( Table[Sales] ),
    FILTER ( Table, [Date] >= TODAY () - 14 && [Date] < TODAY () )
)

Last Month =
CALCULATE (
    AVERAGE ( Table[Sales] ),
    FILTER ( Table, [Date] >= TODAY () - 30 && [Date] < TODAY () )
)

Regards,

Xiaoxin Sheng