Creating a 12 month rolling trend report using Calendar

[espinozan]

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

[Anonymous]

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]

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 · Accepted solution]

Aah, then try something like (untested code) :

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

Filter column by "True".

[espinozan]

@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?

[Salvador]

Hi,

Havent tried it, but... perhaps max?

[espinozan]

Yes!

MAX() worked! 

Thank you both @Anonymous and @Salvador for helping me. I appreciate it so much. Below is my final syntax. 

Then filter visuals by "True."

12M Rolling Trend = 

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

             IF([Date]> MAX([Date]), FALSE(), 

                   TRUE() ) )

[GilbertQ]

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]

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]

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 😄

[espinozan]

Thanks @Salvador,

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