March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
Register NowGet certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now
Good morning,
I want to create a filter that filters the last 13 months of all my calculations in a matrix table (total revenue, growth in absolute values and percentage).
I have created a column in my date table, to do this, but selecting -27 months to avoid losing the growth results if filter only -13 months, it obviously does not filter 13 months in the table
Could you help me to find a better way to do this?
Filter_Last13 Months =
IF(
MONTH(DATEDIFF(
'Creation Date Calendar'[Created Date].[Date],
EOMONTH(
MAX('Creation Date Calendar'[Created Date].[Date]),-27),
MONTH)) >= 12, "Last 13 Months", ""
)
Solved! Go to Solution.
Hi, @3508026
Sorry, my mistake, it was Named Page 1, the order was Page 2. If you drag the Created Date field into Table Visual and keep Year, Month, you'll see that the months of 2023, 2024 will be there, it is by design. So I added additional Year and Month, Month Number fields, and if you take my approach, filtering with segment fields should also work.
Dax:
_Rev =
CALCULATE (
SUM ( 'Creation by Segment'[Annual Rev] ),
ALLEXCEPT (
'Creation by Segment',
'Creation by Segment'[Month],
'Creation by Segment'[Year],
'Creation by Segment'[Segment]
)
)
Best Regards,
Yang
Community Support Team
If there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!
How to get your questions answered quickly -- How to provide sample data in the Power BI Forum
Hi, @3508026
Maybe you can try the method to solve your need.(Page 2)
_YoY_Rev =
VAR _month =
SELECTEDVALUE ( 'Creation by Segment'[Month] )
VAR _lastYearSamePeriodYear =
CALCULATE ( SELECTEDVALUE ( 'Creation by Segment'[Year] ) ) - 1
VAR _YoY_Rev =
CALCULATE (
SUM ( 'Creation by Segment'[Annual Rev] ),
'Creation by Segment'[Year] = _lastYearSamePeriodYear
)
RETURN
DIVIDE ( [_Rev] - _YoY_Rev, [_Rev] )
flag =
VAR _today =
TODAY ()
VAR _startDate =
EOMONTH ( _today, -13 ) + 1
VAR _lastYearYear =
YEAR ( _startDate )
VAR _lastYearMonth =
MONTH ( _startDate )
VAR _monthNumber =
SELECTEDVALUE ( 'Creation by Segment'[MonthNumber] )
VAR _year =
SELECTEDVALUE ( 'Creation by Segment'[Year] )
VAR _flag =
IF (
( _year = _lastYearYear
&& _monthNumber >= _lastYearMonth )
|| ( _year > _lastYearYear ),
1
)
RETURN
_flag
Best Regards,
Yang
Community Support Team
If there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!
How to get your questions answered quickly -- How to provide sample data in the Power BI Forum
Hi @v-yaningy-msft
The solution works for totals, except when I add another level of data for Countries or Segments.
e.g. if I want to show a breakdown by Segments and everything disappears, how can I fix it?
Thank you very much @v-yaningy-msft
Just to be sure, you mention Page 2, but I only see Page1, you meant Page1, right?
Just one more thing about the formula. We are almost there, you are helping me a lot.
I see that the rolling 13 months takes me Nov and Dec I only have data until October of this year, so I only want to take the 13 months from the current month that has data backwards, that is 2023-Oct until 2024-Oct.
I tried it before, but when using a date table it takes all the months of the calendar.
How can I fix that?
Hi, @3508026
Sorry, my mistake, it was Named Page 1, the order was Page 2. If you drag the Created Date field into Table Visual and keep Year, Month, you'll see that the months of 2023, 2024 will be there, it is by design. So I added additional Year and Month, Month Number fields, and if you take my approach, filtering with segment fields should also work.
Dax:
_Rev =
CALCULATE (
SUM ( 'Creation by Segment'[Annual Rev] ),
ALLEXCEPT (
'Creation by Segment',
'Creation by Segment'[Month],
'Creation by Segment'[Year],
'Creation by Segment'[Segment]
)
)
Best Regards,
Yang
Community Support Team
If there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!
How to get your questions answered quickly -- How to provide sample data in the Power BI Forum
Hi, @3508026
After viewing the link, there are no files in it, even after refreshing it.
Best Regards,
Yang
Community Support Team
Hi, @3508026
The same result, don't know the reason. Maybe you can try to use another platform like google drive, etc.
Best Regards,
Yang
Community Support Team
@v-yaningy-msft It's weird, please try this google drive link:
https://drive.google.com/file/d/1YewxMX95ZtLNtyQLTtJ_bcMBDHLFtgt5/view?usp=drive_link
Hi, @3508026
Thanks for rajendraongole1's and Kedar_Pande's suggestions. Didn't see what you were aiming for, what does this last 27 months data do to the last 13 months data. You can share the pbix file without sensitive data and your expected results. Feel free to help you.
Best Regards,
Yang
Community Support Team
Good morning,
I still need your help to solve my issue with the 'Last 13 months' I can't see any option in the chat to attach my pbix file, there is only option to send photo. so is there any other way to do it?
Thanks in advance and regards,
Hi, @3508026
You can try sharing the link containing your pbix file using Google Drive, One Drive, etc.
Here are some notes:
If you are unsure how to upload data please refer to https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...
Please show the expected outcome based on the sample data you provided.
Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...
Best Regards,
Yang
Community Support Team
Hi @v-yaningy-msft this is the correct link: Last13Months_File
Thanks in advance
Hello @v-yaningy-msft , here is the link: PBIFile
I really appreciate your help
Regards,
Hi @v-yaningy-msft
Using 27 months is the only way I have found not to lose my comparative growth calculations based on “SAMEPERIODLASTYEAR”
if I use only 13 months the YoY calculation disappears because obviously I am taking the last 13 months which excludes the same months of the previous year.
_YoY_Creation_rev =
Unfortunately I am not allowed to share my file
I hope you can help me
Filter_Last13Months =
IF(
'Creation Date Calendar'[Created Date] >= EDATE(TODAY(), -13) &&
'Creation Date Calendar'[Created Date] <= TODAY(),
"Last 13 Months",
""
)
If this helped, a Kudos 👍 or Solution mark would be great!
Cheers,
Kedar Pande
www.linkedin.com/in/kedar-pande
Hi @3508026 - You can modify the logic to dynamically select the last 13 months using a combination of TODAY(), DATEDIFF(), and date-related functions to accurately filter your calculations. Here’s a revised version of your Filter_Last13Months formula
Filter_Last13Months =
IF (
'Creation Date Calendar'[Created Date] >= EDATE(TODAY(), -13) &&
'Creation Date Calendar'[Created Date] <= TODAY(),
"Last 13 Months",
""
)
This should correctly filter your matrix to show only the last 13 months, hope this should works.
Proud to be a Super User! | |
Hi @rajendraongole1
I have tried, but when I apply this solution the YoY% calculation (based on the SAMEPERIODLASTYEAR function) appears empty and I understand why, so this is the big challenge I have, that the metrics based on time intelligence do not disappear.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
Check out the November 2024 Power BI update to learn about new features.
User | Count |
---|---|
116 | |
83 | |
76 | |
62 | |
58 |
User | Count |
---|---|
140 | |
122 | |
105 | |
94 | |
90 |