Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get 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

Reply
3508026
Helper II
Helper II

PowerBI Last 13 months filters

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", ""
)
tbl.jpg

1 ACCEPTED 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.

vyaningymsft_0-1730180055150.png

vyaningymsft_1-1730180435815.png

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

View solution in original post

18 REPLIES 18
v-yaningy-msft
Community Support
Community Support

Hi, @3508026 
Maybe you can try the method to solve your need.(Page 2)

vyaningymsft_0-1729850111526.png

_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?
Screenshot 2024-10-27 170515.jpg

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.

vyaningymsft_0-1730180055150.png

vyaningymsft_1-1730180435815.png

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

v-yaningy-msft
Community Support
Community Support

Hi, @3508026 

After viewing the link, there are no files in it, even after refreshing it.

vyaningymsft_0-1729818925453.png

Best Regards,
Yang

Community Support Team

Hi @v-yaningy-msft 

Please try this link: PBILast13Mo 

Hi, @3508026 

The same result, don't know the reason. Maybe you can try to use another platform like google drive, etc.

vyaningymsft_0-1729842086152.png

Best Regards,
Yang

Community Support Team

v-yaningy-msft
Community Support
Community Support

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,

Just in case you can not open the above link: PBIFile 

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 =

 VAR _CY = [_Total_revenue]
 VAR _PY = CALCULATE([_Total_revenue],SAMEPERIODLASTYEAR(PBI[Created Date].[Date]))
 RETURN
 DIVIDE (_CY-_PY,_PY)

3508026_0-1728394303684.png

Unfortunately I am not allowed to share my file

I hope you can help me

Kedar_Pande
Memorable Member
Memorable Member

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

rajendraongole1
Super User
Super User

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.





Did I answer your question? Mark my post as a solution!

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.

tbl2.jpg


Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

Check out the November 2024 Power BI update to learn about new features.

October NL Carousel

Fabric Community Update - October 2024

Find out what's new and trending in the Fabric Community.