Forum Discussion

Icon for Helper V rank

Helper V

9 years ago

Solved

Dataset that compares moving average with different period

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
9 years ago
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

5 Replies

v-huizhn-msft
Microsoft Employee
9 years ago
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
  Helper V
  9 years ago
  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
    Microsoft Employee
    9 years ago
    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

Related content