Forum Discussion

espinozan's avatar
espinozan
Helper I
9 years ago

Rolling Calculation

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.

 

Need Help
Show and Tell
Tutorial Requests

4 Replies

    • espinozan's avatar
      espinozan
      Helper I
      9 years ago

      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?

       

      • cs_skit's avatar
        cs_skit
        Resolver IV
        9 years ago

        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.