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 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".
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

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

      Thanks Salvador,

       

      The syntax works however, Its showing my most recent date as 89 instead of 1 and i'm not sure why.