Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago

Calculate average based on weeks

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. 

3 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

    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's avatar
      Anonymous
      Not applicable
      7 years ago

      Anonymous, I do have a proper date table set up

       

      • Anonymous's avatar
        Anonymous
        Not applicable
        7 years ago

        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