Need Measure to only show last 3 Years of Data

[bdehning]

Here is a Power Bi Report.   Can someone create a measure I can use as a filter to show the last 3 years for any customer.  Customers are selected from Slicer on Selection Page which populates Page 1 columns.    

https://drive.google.com/file/d/1icSIA3aShqN3Ddb2p5WFO2vManfgfJhu/view?usp=drive_link

[Ashish_Mathur]

Hi,

I cannot understand your question.  Show the expected result very clearly.

[bdehning · Accepted solution]

Here is the File that I did get to work.  I took Last3YearsFilter below and put it in ChatGPT and got the measure corrected.   

https://drive.google.com/file/d/104cWzIh7ei2zwQzFjXR1LkFAy6odeE7X/view?usp=sharing

[bdehning]

Sorry it should be this file.    https://drive.google.com/file/d/12ndhq72lIESoi2t4kA4uo02k0weMwaaG/view?usp=sharing

[bdehning]

I have not received a valid measure yet.   

[HarishKM]

@bdehning Hey,
you can use below measure 

Last3YearsFilter =
VAR MaxYear = MAX(CustomerTable[Year]) -- Get the maximum (most recent) year selected or available
VAR Last3Years = MaxYear - 3 -- Calculate the year that is 3 years before the most recent year
RETURN
IF (
CustomerTable[Year] >= Last3Years && CustomerTable[Year] <= MaxYear, -- Filter for the last 3 years
1, -- Return 1 if the year is within the last 3 years
0 -- Return 0 otherwise
)

If you use aboce measure then you will get 3 year worth record when you select 1 in filter.

Thanks
Harish M
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly and give Kudos if helped you resolve your query

[bdehning]

I put this in as Measure  

Last3YearsFilter =

VAR MaxYear = MAX('Table'[Year]) -- Get the maximum (most recent) year selected or available

VAR Last3Years = MaxYear - 3 -- Calculate the year that is 3 years before the most recent year

RETURN

IF (

'Table'[Year] >= Last3Years && 'Table'[Year] <= MaxYear, -- Filter for the last 3 years

1, -- Return 1 if the year is within the last 3 years

0 -- Return 0 otherwise

)

But I get where the two [Year] are bold below    A single value for column 'Year' in table 'Table' cannot be determined. This can happen when a measure formula refers to a column that contains many values without specifying an aggregation such as min, max, count, or sum to get a single result.

'Table'[Year] >= Last3Years && 'Table'[Year] <= MaxYear, -- Filter for the last 3 years

[rajendraongole1]

Hi @bdehning - as per the shared pbix file, there is no last three years data. i have created a measure plesae check added in visual filter too. 

Show Last 3 Years =

VAR CurrentYear = YEAR(TODAY())

RETURN

IF(

    MAX('table'[Year]) >= CurrentYear - 2,

    1,

    0

)

Please find the attached pbix file.

[bdehning]

That is why I need a measure.  There is 3 years or more of data but most are not within the last 3 calendar years.