Rolling Interval Calculation

[Anonymous]

Hi,

My dataset is as follows

Year     Month      Day_of_Month     Values

   .           .                        .                     .  

2017     12                     31                    5

2018     1                       1                     10

2018     1                       2                     20

and so on

I want to calculate Rolling Interval Calculation on basis of filter rolling interval (1-12)

If i select interval 1 then formula should give aggegated measure for past 30 days,

if i select interval 2 then formula should give aggregated measure for past 60 days and so on.

So far i am using the below mentioned formula which aggregates monthwise only i.e for 1-jan-2018 the calculation should show sum of values for 2-dec-2017 to 1-jan-2018. My calculation currently aggregates only inside a month so for 1-jan-2018 it shows the same value 10.

SumValues=Sum([Values])

Rolling inteval calculation :-IF([Selected Interval]=1,
CALCULATE([SumValues],
DATESINPERIOD(Table[DATE],LASTDATE(Table[DATE]),-30,DAY)
),
IF([Selected Interval]=2,
CALCULATE(Table[SumValues],
DATESINPERIOD(Table[DATE],LASTDATE(Table[DATE]),-60,DAY)
)
)

Please help.

Thanks in advance

Power BI User

[LivioLanzo]

Hi @Anonymous,

the correct way to handle this is through the creation of a Date dimension table but it does not look like it is the case for you?

[Anonymous]

I don't have a date dimension , but a date column in  my Table

Power BI User

[LivioLanzo]

@Anonymous

you need to start by creating one: http://radacad.com/create-a-date-dimension-in-power-bi-in-4-steps-step-1-calendar-columns

[Anonymous]

Hi Liviolanzo,

I have created date dimension.

Now what do i need to do to calculate rolling interval.

Power BI User

[LivioLanzo]

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)

[Anonymous]

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

[LivioLanzo]

Can you show a screen shot or share your file? You may need to created a disconnected table

[Anonymous]

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.

Power BI File

Data