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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
HLVW
Helper I
Helper I

Measure for LY that ignores select filter

Hi.
 
I am trying to calculate a measure for a matrix, showing total LY figures. I have 2 slicers, Month & Year. If I select f.ex. 2024 and month May, I would like the new measure to show full 2023 and not only YTD May 2023.
I have tried with ALLEXCEPT, but with no luck....
Current measure: LY Full year = CALCULATE(-SUM('Finance Transactions_V'[Amount]), SAMEPERIODLASTYEAR(Date_V[Date]),ALLEXCEPT(Date_V, Date_V[Year Number]))
 
Thanks a lot in advance.
1 ACCEPTED SOLUTION
_AAndrade
Super User
Super User

Hi,

 

Please try this:

VAR Year = MAX(Date_V[Year Number])

RETURN

CALCULATE(
    "Your Sum Measure",
    REMOVEFILTERS(Date_V),
    DATESBETWEEN(Date_V[Date],DATE(Year -1,1,1),DATE(Year -1,12,31))
)






Did I answer your question? Mark my post as a solution! Kudos are welcome.

Proud to be a Super User!




View solution in original post

4 REPLIES 4
_AAndrade
Super User
Super User

Hi,

 

Please try this:

VAR Year = MAX(Date_V[Year Number])

RETURN

CALCULATE(
    "Your Sum Measure",
    REMOVEFILTERS(Date_V),
    DATESBETWEEN(Date_V[Date],DATE(Year -1,1,1),DATE(Year -1,12,31))
)






Did I answer your question? Mark my post as a solution! Kudos are welcome.

Proud to be a Super User!




It works perfectly! Thanks a lot.

danextian
Super User
Super User

Hi @HLVW ,

Do you intend to show the value for each past x months as well and not just the correct selected month? You will need a disconnected dates/calendar table for that as using the one related to your fact table will just show what is selected. Please see attached sample pbix.

danextian_0-1711448092887.png

 










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


Proud to be a Super User!









"Tell me and I’ll forget; show me and I may remember; involve me and I’ll understand."
Need Power BI consultation, get in touch with me on LinkedIn or hire me on UpWork.
Learn with me on YouTube @DAXJutsu or follow my page on Facebook @DAXJutsuPBI.

Thanks a lot for your reply. I would like a matrix visual as below, where the right column is full last year in spite of filters. I already have a date table, but this is connected...

HLVW_0-1711448454771.png

 

Helpful resources

Announcements
Fabcon_Europe_Social_Bogo

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

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

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.