Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to Solution.
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
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
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?
Hi,
Havent tried it, but... perhaps max?
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() ) )
Hi @espinozan
Do you perhaps have some sample data that we can look at or an image for what you are trying to achieve?
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.
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 😄
Thanks @Salvador,
The syntax works however, Its showing my most recent date as 89 instead of 1 and i'm not sure why.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
115 | |
100 | |
88 | |
69 | |
61 |
User | Count |
---|---|
151 | |
120 | |
103 | |
87 | |
68 |