Moving Avg not working as intended

[rogerdea]

Hello

I have done a moving 30 day average for the past 30 days, but i am not getting the expected results.  This is my DAX:

4 Week Avg (Proc Date) = 
CALCULATE(
            COUNT(Metadata[IncidentId]), 
            DATESINPERIOD(Metadata[Completed Date],LASTDATE(Metadata[Completed Date]),-30,DAY)
         )
/ 
CALCULATE(
            DISTINCTCOUNT(Metadata[Week Completed]),
            DATESINPERIOD(Metadata[Completed Date],LASTDATE(Metadata[Completed Date]),-30,DAY)
         )

But my 4 week average is the same figure as number of records each week.

I can't upload the data because of confidentiality, but have i done anything obviously wrong?

Thanks

[rogerdea]

Actually the number of records is not calculating correctly either.  So both calulations are not correct.

[Fowmy]

@rogerdea 

Can you create two measures for each part of your calculations and check the results?

CALCULATE(
            COUNT(Metadata[IncidentId]), 
            DATESINPERIOD(Metadata[Completed Date],LASTDATE(Metadata[Completed Date]),-30,DAY)
         )

CALCULATE(
            DISTINCTCOUNT(Metadata[Week Completed]),
            DATESINPERIOD(Metadata[Completed Date],LASTDATE(Metadata[Completed Date]),-30,DAY)
         )

[rogerdea]

@Fowmy  great suggestion, i can see the problem:

It's calculating the number of weeks as 1.  (column named 2)

I definitely have more than one distinct week in my data looking over the past 30 days:

any ideas what i did wrong?

[rogerdea · Accepted solution]

So i think i found  better way to do it which is working fine:

Processing (14 day moving avg) = 
AVERAGEX( 
          DATESBETWEEN(Metadata[Completed Date], MAX(Metadata[Completed Date]) -14, MAX(Metadata[Completed Date])),
          CALCULATE(COUNT(Metadata[IncidentId]))
          )

Note - i decided to change to a 14 day moving average but the logic works.