Forum Discussion
Summarize Table by Fields and Implementing Calculations
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.
Hi arhomberg ,
Solving with DAX will be relatively simple.
1.Create a calendar table, and create relationship between it and the fact table
2.Current Avg=CALCULATE(AVERAGE(Table[Percent_Correct]),DATEADD('Calendar'[Date],0,MONTH))
Prev Month Avg=CALCULATE(AVERAGE(Table[Percent_Correct]),DATEADD('Calendar'[Date],-1,MONTH))
Percent change=DIVIDE([Current Avg]-[Prev Month Avg],[Prev Month Avg])
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 Thanks so much for this!
A quick question to clarify, what exactly should I be putting into the Calendar table? Would this be all the dates which the quizes exist, or just the current date? Sorry for the ignorance, but I am new to DAX. Can I also assume that the formulas you provided above are measures?
Thanks again!
Hi arhomberg ,
You can fill in the date manually, or you can refer to a date that exists in an existing table.
You can create a calendar table like
Table 2 = CALENDAR(DATE(2021,1,1),DATE(2021,12,31))Or
Table 2 = CALENDAR(MIN('Table'[Date]),MAX('Table'[Date]))DAX: CALENDAR function
Also there‘s a function called CALENDARAUTO you can use.
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 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!
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.