Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago

12 Months rolling average

Hello Community,   I hav a table (Case) with : 1. Case ID 2. Casedatevalue which contains date and is of date type   I want to have count of case id by month and also rolling average for 12 mon...
Need Help
Ashish_Mathur's avatar
Ashish_Mathur
Icon for Super User rankSuper User
3 years ago

Hi,

You may download my PBI file from here.

Hope this helps.

Anonymous's avatar
Anonymous
Not applicable
3 years ago

Ashish_Mathur  Thank you so much for your solution and prompt response.

 

Just want to understand some points:

 

1. "ABCD",[Case count]),[ABCD]) . Couls you Please explain what is "ABCD" as it's not table or column or measure in the file.
2.  As discussed, as its is showing rolling average correctly as expected. As Discussed, if I select date as slicer and select 2023 or 2022, then will it give me last 12 month rolling average from the current month of 2023?

 

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

    You are welcome.  ABCD is the title of the virtual column.  You may give any name instead of ABCD.  Glad that the formula is working fine.

    • Anonymous's avatar
      Anonymous
      Not applicable
      3 years ago

      Ashish_Mathur The average are correct. But as it is 12 month rolling average for example Feb 2023, it should go back 11 month adds the counts from March 2022 to Feb 2023 (62) and divide by 12 which will be 5.16666.

      As per your explanation it is doing rolling average but not considering 12 month rolling average for example for Feb 2023 it is summing the count from Jan 2022 to feb 2023. 

       

      • Anonymous's avatar
        Anonymous
        Not applicable
        3 years ago

        Ashish_Mathur  Even for 

        Even for Oct 2022 and Nov 2022 value shouldn't be the same as last 12 month will change for ex. for oct2022(Oct 2022 + last 11 month sum/12) .

        Please assist on the same.

    • Anonymous's avatar
      Anonymous
      Not applicable
      3 years ago

      Ashish_Mathur  Request your kind consideration.