Blank values causing the moving average calculation to be blank

[Anonymous]

Hi All,

I have a measure that utilizes Sumx 

Avg Fees (K) = SUMX(

VALUES(pipeline[OpportunityID]),

AVERAGE(pipeline[EstimatedFees])

)/1000

And I use this measure in my Moving average measure

T6W AVG WINS = CALCULATE([Avg Fees (K)],DATESINPERIOD(pipeline[Nearest Friday],LASTDATE(pipeline[Nearest Friday]),-42,DAY))/6

Nearest Friday is my date. 

The results are as follows. The week of 05/21/2021 is missing from the Moving average since there is no Avg Fees that week. But I would like it to still take the moving 6 week average even of the week with 0 avg fees.

Please advise 🙂

[Ashish_Mathur · Accepted solution]

Hi,

It will be ideal to have a Calendar Table with a relationship from the Nearest Friday field of the Pipeline table to the Date field of the Calendar Table.  Please also show the exact result you are expecting for 5/28/2021.  Share the download link of your PBI file.

[Anonymous]

Hi Ashish,

That was it, all I did was join Nearest Friday with a Date table and then I created my measures off of that table instead of the Neasrest Friday date and it worked! 
Thank you!

[Ashish_Mathur]

You are welcome.