Forum Discussion
Impactful IndividualConvert a table without date into a 6 Month (6 steps Moving Average
Hello,
my table consist of three columns. One is the YearMonthKey, Date and another percentage where I want to get my moving average from:
I marked my calendar table as "date table" but the quick measures don't accept my date or YearMonthKey as "Date field".
So instead I hope there is a way to calculate the 6month or better said 6 Steps back as a calculated moving average without the messy date functions in form of another calculated column.
Hi Applicable88
I know you want to calculate the 6 month rolling average of sum of percentages. And your Date column is not in date type.
Try my way to achieve your goal. I build a sample data model like yours to have a test.
Sample:
Add a Rank column by dax, and we don't need to use date type column.
Rank = RANKX('Table','Table'[YearMonthKey],,ASC,Dense)Measure:
Rolling 6 Avg = VAR _EndRank = MAX('Table'[Rank]) VAR _StartRank = _EndRank-6 VAR _Rolling6Sum = SUMX(FILTER(ALL('Table'),'Table'[Rank]<=_EndRank&&'Table'[Rank]>_StartRank),'Table'[Percentage]) VAR _Avg = DIVIDE(_Rolling6Sum,6) Return _AvgResult is as below. Rolling 6 Avg in 202105 = 55,60%.
Best Regards,
Rico Zhou
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
5 Replies
Hi Applicable88
I know you want to calculate the 6 month rolling average of sum of percentages. And your Date column is not in date type.
Try my way to achieve your goal. I build a sample data model like yours to have a test.
Sample:
Add a Rank column by dax, and we don't need to use date type column.
Rank = RANKX('Table','Table'[YearMonthKey],,ASC,Dense)Measure:
Rolling 6 Avg = VAR _EndRank = MAX('Table'[Rank]) VAR _StartRank = _EndRank-6 VAR _Rolling6Sum = SUMX(FILTER(ALL('Table'),'Table'[Rank]<=_EndRank&&'Table'[Rank]>_StartRank),'Table'[Percentage]) VAR _Avg = DIVIDE(_Rolling6Sum,6) Return _AvgResult is as below. Rolling 6 Avg in 202105 = 55,60%.
Best Regards,
Rico Zhou
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Applicable88Hello Anonymous!
exactly what I wanted in case the date isn't recognized as a date. That way I can also use Datetimekeys as dimension. Thank you very much!
Best.
- amitchandak
Super User
Applicable88 you need to have date column in date table
Create it with help from the calendar
if you only have year month in your fact, create a date and join
date =left([Yearmothkey],4), right([Yearmothkey],2),1)
you can rolling 6 like the example
Rolling 6 = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date ],MAX('Date'[Date ]),-6,MONTH))
To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :radacad sqlbi My Video Series Appreciate your Kudos.
- Applicable88
Hello amitchandak ,
I followed your instruction. Datetable is marked, YearMonthKey is also a column in that DateTable.
I got this return:
I changed your function according to my values and used the average function:
Rolling 6 = CALCULATE(AVERAGE('MA-Table'[Percentage]),DATESINPERIOD(Mastercalendar[Date],MAX(Mastercalendar[Date]),-6,MONTH))Expected Moving Average value for 202105 should be 55,60%.But if I go back using sum to sum the percentage I would get this:What am I missing here?I have the same issue with yours.
The difference between our case is that Over 30 DPD Percentage(P), Over 30 DPD Percentage(S), AND Over 30 DPD Percentage are Measures instead of Columns. Please help if you have a solution.
