Rolling Calculation

[espinozan]

Hello everyone,

I currently have a rolling trend in my report and it works great. However, I'm trying to get my visuals to show one year worth of data using this trend but for some reason it currently shows 5 Qtrs instead of 4. My data ranges from January 2017 to April 2017. My trend is based on a Boolean Statement. If True then apply the 12M Rolling Trend, If false, show all data. How can I get it to show Qtr 3 and 4 in year 2016 and Qtr 1 and 2 in 2017? The "True" currently ranges from April 1 2016 to April 1 2017.

12M Rolling Trend = 

       IF('Calendar'[Date]< MAX('Calendar'[Date])-365,FALSE(), 

            IF('Calendar'[Date]> MAX('Calendar'[Date]), FALSE(), 
                    
                   TRUE() ) )

Thank you for your help.

[cs_skit]

If you use builtin time intelligence you might want to take a look at using custom time intelligence instead.

http://databear.com/2016/11/08/power-bi-tip-dynamic-calendar-table/

There you can easily add overall Quarter numbers and make sure you show only 4 quarters ever by doing easy range selection like CurrentQuarter-3.

[espinozan]

Hi @cs_skit,

Thank you for your reply, I've created the table in the Query Editor. But im not sure how this could be implemented into my data. Do you mind explaining further? I'm a begginer with Power BI so all of this is kind of new to me.

What do you mean I could add overall Quarter numbers by a range selection?

[v-huizhn-msft]

Hi @espinozan,

Have you resolved your issue? If you have, please mark the right reply as answer. If you have not, could you please share more details or your sample table, and list the expected result for further analysis?

Best Regards,
Angelia

[cs_skit]

Yes in the calendar table you add an index for the quarters. Then you need a measure like "This Quarter" and just select by Quarter between this quarter and quarter - 3.

You could even do more complex stuff like join your calendar table with a quarter table and a fiscal year table.