Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Blank values causing the moving average calculation to be blank

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 🙂

 


 

Need Help
  • Ashish_Mathur's avatar
    Ashish_Mathur
    5 years ago

    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.

3 Replies

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Icon for Super User rankSuper User
    5 years ago

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

      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!