Only display last 3years record with year end condition...

[NickzNickz]

Hi ...

I need to make a line chart with trend lines. Below is a record (sample data) with the accumulated amount...

 For December, there will be a month 13 (audited account).

How can I only display only last 3 recent years (full year)... If the date (December) has month 13, the display should pick up that month instead of month 12...

Below is the measure that I have currently to show the data for KPI Card:

Receivables (RM) = 

VAR _Result = CALCULATE(
        SUM( fin_table[Receivables (M)] ),
        fin_table[Date] = MAX( fin_table[Date] ) && ( fin_table[Month] )

)
RETURN
IF(ISBLANK(_Result),"-",_Result)

Your help is highly appreciated.... 

 Thank you.

Regards,

NickzNickz

[Mahesh0016]

@NickzNickz  as per my understanding you need the last 3 year data before 2023.
For E.g current year is 2023 and you need three years of data for 2020,2021 and 2022. if I am not wrong.

[NickzNickz]

@Mahesh0016 ,

Correct because this year is still not a full year...

[Mahesh0016]

@NickzNickz Please try below Dax:

Last 3 Years =

 VAR MX_date = MAX('Date'[Date])

 RETURN

 CALCULATE([Total Sales],DATEADD('Date'[Date],-3,YEAR))

I hope this Helps you! Thank You!!

[NickzNickz]

@Mahesh0016 ,

I have tested based on your recommendation and the result shows only data for 2023.

Regards,

NickzNickz

[Mahesh0016]

@NickzNickz you want to filter out the current year and display only before the current year data right?

[NickzNickz]

@Mahesh0016 , year 2023 yet to complete/full year, shall not be count ..

[Mahesh0016]

@NickzNickz  I hope this helps you. Thank You!!

CALCULATE([Total Sales],'Date'[Year]<MAX('Date'[Year])).