Forum Discussion

arhomberg's avatar
arhomberg
Icon for Helper I rankHelper I
5 years ago
Solved

Summarize Table by Fields and Implementing Calculations

Hello everyone,   I am currently working to figure out the best way to combine and summarize some time series data I have. We are currently tracking quiz scores and would like to eventually see an ...
Need Help
  • Anonymous's avatar
    Anonymous
    4 years ago

    Hi arhomberg ,

     

    Sorry about my mistake. DATEADD needs to put the date column into the visual to work.

     

    I made a simple example. I used the simplest function to filter this month and last month. Please check the screenshots and attachments.

    Current Avg = CALCULATE(AVERAGE('Table'[Percent_Correct]),FILTER('Calendar',YEAR([Date])=YEAR(TODAY())&&MONTH([Date])=MONTH(TODAY())))
    Prev Month Avg = CALCULATE(AVERAGE('Table'[Percent_Correct]),FILTER('Calendar',YEAR([Date])=YEAR(TODAY())&&MONTH([Date])=MONTH(TODAY())-1))
    Percent change = DIVIDE([Current Avg]-[Prev Month Avg],[Prev Month Avg])

     

    Hope to help you. Sorry again.

     

     

    Best Regards,

    Stephen Tao

     

    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 arhomberg 

 

Where is your previous month data, in the same table? You can add a Month column, then group by course name and month, so you can get the avg for each month

arhomberg's avatar
arhomberg
Icon for Helper I rankHelper I
5 years ago

Hello Anonymous 

 

Thanks for the advice! Since the data is a list of all the quiz scores, the 'previous month' data is all in the same table, it would just be found by taking the current average for -30 days (or -1 month). I could do as you suggest and create a month column, group by the course name and month to find teh current averages, but would there be a way then add the calculation for the average of the previous month as well?