Forum Discussion

arhomberg's avatar
arhomberg
Helper I
4 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.

arhomberg's avatar
arhomberg
Helper I
4 years ago

Anonymous Thanks for this! 

 

I was using the CALENDARAUTO() function, but just wan't to confirm this was what I should have been using. I am experiencing an issue when trying to implement your initial advice from above. When I create the Calendar table, add a relationship to the fact table, and create the measures, the table appears blank. 

 

 

When I delete the relationship and let the tables standalone, the table loads the information in the proper format, but the calculation for the previous month is not coming through correctly (even if I trouble shoot and try -6 months, it stays the same).

 

 

I am pretty sure I followed your directions and entered the measures correctly:

 


 

 

Any advice on why these calculations might not be working as expected would be wonderful.

 

Thank you!

Anonymous's avatar
Anonymous
Not applicable
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.

  • arhomberg's avatar
    arhomberg
    Helper I
    4 years ago

    Anonymous This is great, thanks for this!