Forum Discussion

espinozan's avatar
espinozan
Icon for Helper I rankHelper I
9 years ago
Solved

Creating a 12 month rolling trend report using Calendar

Hello all,

 

I've been trying to research the best way to create a 12 month rolling trend report in Power BI. Somehow, I can't seem to find what I'm looking for. So far, I've notived most use calculated measures and filtering to do so, however, I'm not sure if this is the best way to implement it into my report.

 

As of now, I have a report that provides visuals from a set of data. I'm trying to create a rolling trend with my data by using the Calendar table in Power BI, where it will only read the data from the past 12months Vs. all data in spreadsheet. Therefore, my visuals will only reflect the current 12 months worth of data. 

 

Thank you in advance.

Regards

Need Help
Show and Tell
Tutorial Requests
  • Anonymous's avatar
    Anonymous
    9 years ago
    Aah, then try something like (untested code) :

    =IF([Date]<Today()-365;False();IF([Date]>Today();False();True()))

    Filter column by "True".

10 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    9 years ago

    Try this calculated table in your Date table (with small adjustments works with any month range and/or offset):

     

    =
IF (
    [Date]
        < ( EOMONTH ( NOW (); -12 ) + 1 );
    FALSE ();
    IF ( [Date] > EOMONTH ( NOW (); 0 ); FALSE (); TRUE () )
)

     

    /RSK

    • espinozan's avatar
      espinozan
      Icon for Helper I rankHelper I
      9 years ago

      Hi Anonymous,

       

      Thank you for your help! I trired the synatax but for some reason its not pulling 365 days worth of data, what can i do to fix this issue?

      • Anonymous's avatar
        Anonymous
        Not applicable
        9 years ago
        Aah, then try something like (untested code) :

        =IF([Date]<Today()-365;False();IF([Date]>Today();False();True()))

        Filter column by "True".
  • GilbertQ's avatar
    GilbertQ
    Icon for Super User rankSuper User
    9 years ago

    Hi espinozan

     

    Do you perhaps have some sample data that we can look at or an image for what you are trying to achieve?

    • espinozan's avatar
      espinozan
      Icon for Helper I rankHelper I
      9 years ago

      I don't have an image GilbertQ, I just have the idea but im not sure if it's possible to achieve in Power BI. So I basically want to use this calendar to create a series of dates that then feed into my report. The report consists of sales revenue and expenses. Am I being clear? Not sure if that makes sense.

      • Salvador's avatar
        Salvador
        Icon for Responsive Resident rankResponsive Resident
        9 years ago

        Hi,

         

        I'd create this column

         

        12 month rolling=  

            SWITCH ( 

            TRUE (); 

            Now() < calendar[date]; DATEDIFF ( NOW();calendar[date]; DAY )* -1; 

            Now() > calendar[date]; DATEDIFF ( calendar[date]; Now(); DAY ); 

            0)

         

        Then as a filter, apply "is greater or equal to... 365 :D