Forum Discussion

espinozan's avatar
espinozan
Helper 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 calcu...
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".
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
    Helper 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".
      • espinozan's avatar
        espinozan
        Helper I
        9 years ago

        Anonymous 

         

        It works, but I think my problem is the 'NOW()' since the most recent date in my data is 04/01/2017. What could I use in place of it but also have it update when I load new data?