Dataset that compares moving average with different period

[RMV]

Hi,

I need to represent a report that shows weekly trend of YTD average of a sales per region. Any advise on how can I use Power BI to help me?

This is the example

Region    Day    Sales Amount

A             1         1000

A             2         1500

A             3         700

B             1          500

B             2          700

After Day 7, there should be a table that calculate Sales Amount Average from Day 1 to Day 7 per region

Region    Sales Amount Average Day 1-7

A             1300

B             700

After Day 14, the table should calculate Sales Amount Average from Day 1 to Day 17 per region, and compare it with Sales Amount Average Day 1-7

Region     Sales Amount Average Day 1-14       Sales Amount Average Day 1-7

A               1400                                                      1300

B                650                                                        700

Thanks

RMV

[v-huizhn-msft]

Hi @RMV,

Althrough you give an detailed example, I still need to confirm several information.

 >>After Day 7, there should be a table that calculate Sales Amount Average from Day 1 to Day 7 per region

For Region A, how to calculate 1300? based on my mind, it shoud be (1000+1500+700)/3=1067.

In addition, how to get 1400 and 650 after day 14? Could you please share more details for further analysis?

Best Regards,
Angelia

[RMV]

Hi @v-huizhn-msft,

The numbers were just an examples. The average should calculate the numbers from Day 1-7, Day 1-14, Day 1-21 etc

The first table is also just an example to show that the sales is keep in raw data, day per day transactions, and not using the complete data of all days to keep my post short. Hope this is clear.

Waiting for input from you.

Thanks

[v-huizhn-msft · Accepted solution]

Hi @RMV,

You can create a measure in your resource table using the formula.

Sales Amount Average Day 1-7=CALCULATE(MAX(Table[running]),FILTER(Table,AND(Table[Day]<=7,Table[Day]>1)))
Sales Amount Average Day 1-14=CALCULATE(MAX(Table[running]),FILTER(Table,AND(Table[Day]<=14,Table[Day]>1)))

Then create a table visual, select [Region] and the measures as value levels.

Best Regards,
Angelia

[RMV]

Hi @v-huizhn-msft,

Tried again with you DAX formula suggestion, and i got it worked!

Thanks a lot!

Regards,

[RMV]

Hi @v-huizhn-msft,

I'm not used with Measure column, and need more some explanation. I added 2 Measures to the table, and use them as Value in the visual, as you said. But it didn't shown anything. Need your advise.

Thanks,

RMV