Calculate average based on weeks

[Anonymous]

The following returns the number of sales for week 1 by poduct:

COUNTX(CALCULATETABLE('SALES REPORT','SALES REPORT'[WEEK NUM]=1),[Count of Gross Sales]) 

1. I would like to create a calculation that shows the average of all (52) weeks

2. I would also like to reate a moving average of the last 4 weeks. 

[Anonymous]

Mate, forget about creating what you want without a proper DATE TABLE. You can't do this based only on the fact table.

I'll give you a hint. Try to post a full description of the model and, if possible, also some sample data.

Best

Darek

[Anonymous]

@Anonymous, I do have a proper date table set up

[Anonymous]

You do?

Well, it does not look like that at all. Your measure 

COUNTX(
    CALCULATETABLE(
         'SALES REPORT',
         'SALES REPORT'[WEEK NUM] = 1
    ),
    [Count of Gross Sales]
)

does not use the Date table to move back and forth in time. It uses some WEEK NUM that's stored in your fact table. Please try to understand that if you go against the rules of proper design, you're shooting yourself in the foot.

But it's your call... 🙂

Best

Darek