Forum Discussion
Rolling Interval Calculation
Hi Liviolanzo,
I have created date dimension.
Now what do i need to do to calculate rolling interval.
Power BI User
Hi Anonymous
perfect! Now you can relate the Date dimension with the fact table if not already done. Afterwards you need to mark it as a Date table
Then you just need to change the filter within CALCULATE to point to the date dimension
DATESINPERIOD(DateTable[DATE],MAX(DateTable[DATE]),-60,DAY)
Hi LivioLanzo,
I tried doing this but it still gives me same result.
However while going through few test sets i realized this is not the problem.
Actually whenever i am using side filters (Year , Month , Day of Month) , the data set is filtered out hence it does not calculate rolling.
For example : if i select complete 2018 , then calculation is correct from 31 Jan 2018 onwards for Rolling Interval-1
If i select Feb-2018 only , data for Jan is filtered out , hence it gives sum of only february which is not the required result.
How do i make the filters work with the calculation.
Power BI User
Can you show a screen shot or share your file? You may need to created a disconnected table
Hi LivioLanzo,
I have attached Power BI File and Excel data source.
Value Rolling Average RI is the final required measure.
It gives correct data values when i select the complete data set that is year 2017 and 2018 both.
If i select only 2018 and check data values for rolling interval-1 , Month-January-2018 (i.e data for past 30 days) , it shows incorrect results because data for dec-2017 is filtered out.
I want it work with the side filters that is if user wants to select only one month and view data , it should give correct rolling average.
Please let me know if the attachment does not open.