Forum Discussion

Harry_Tran's avatar
Harry_Tran
Icon for Helper III rankHelper III
5 years ago
Solved

7 days rolling average for missing values

Hello everyone, I need help to calculate 7 Days Rolling Average for missing data. I have MAIN table with Date, Users, Points, Categories, Subcategories, and Products columns. I also hace Calendar ta...
Need Help
  • Ashish_Mathur's avatar
    Ashish_Mathur
    5 years ago

    Hi,

    You may download my PBI file from here.

    Hope this helps.

  • Anonymous's avatar
    Anonymous
    5 years ago

    Hi Harry_Tran ,

     

    Sorry for late reply. You can try a calculated column like this:

     

    index = RANKX(ALLSELECTED('CASE'[Date]),'CASE'[Date],,ASC)
     
    Result:
     

     

    Hope that's what you were looking for.

    Best Regards,

    Yuna

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

     

Anonymous's avatar
Anonymous
Not applicable
5 years ago

Hi Harry_Tran ,

 

Sorry for late reply. You can try a calculated column like this:

 

index = RANKX(ALLSELECTED('CASE'[Date]),'CASE'[Date],,ASC)
 
Result:
 

 

Hope that's what you were looking for.

Best Regards,

Yuna

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

 

Harry_Tran's avatar
Harry_Tran
Icon for Helper III rankHelper III
5 years ago

Hi Anonymous ,

Thank you for your response

I created an index column as you suggested and it gave me result like this

it jumped from 2 to 6 and skip 3,4,5

Do you know why?

Thank you

 

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    Hi Harry_Tran ,

     

    Will it happen after you sort the date column?

    Please select the index column and verify if the contained values are only 2 and 6.

     

    Best Regards,

    Yuna