cancel
Showing results 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

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]))

1 ACCEPTED SOLUTION
Super User

Hi,

``````VAR Year = MAX(Date_V[Year Number])

RETURN

CALCULATE(
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!

4 REPLIES 4
Super User

Hi,

``````VAR Year = MAX(Date_V[Year Number])

RETURN

CALCULATE(
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!

Helper I

It works perfectly! Thanks a lot.

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.

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.
Helper I

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

Announcements

#### 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 Monthly Update - June 2024

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

#### 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.

#### New forum boards available in Real-Time Intelligence.

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

Top Solution Authors
Top Kudoed Authors