Same filter for all charts include trend for past 5 years

[ru_1]

Hi, I have an requirement from client that they need to have a financial year filter(slicer) for the dashboard but I need to show it for trend as well but for trend chart only it should show past 5 year data as per measure for example 

If 2024-2025 is selected then card and bar charts should show for that year values only but for trend it should show 2024-25,2023-24 so on..

TIA :)) 

[Anonymous]

Thanks for the replies from Bibiano_Geraldo, Angith_Nair and danextian.

Hi @ru_1 ,

Have you solved your problem? If it is solved, please mark the replies that help you as solutions, it will be helpful for other members of the community who have similar problems as yours to solve it faster. If there are any more questions, please feel free to let us know. Thanks a lot!

Best Regards,
Zhu

[Bibiano_Geraldo]

Hi, @ru_1 ,

You'll need to create two diferent measures to achieve your goal as explained bellow:

Create a Measure for the Cards and Bar Charts by this DAX:

SelectedYearMeasure = 
CALCULATE(
    [YourMeasure],
    'DateTable'[FinancialYear] = SELECTEDVALUE('DateTable'[FinancialYear])
)

Now you can create a Measure for the Trend Chart by this DAX:

TrendMeasure = 
VAR SelectedYear = SELECTEDVALUE('DateTable'[FinancialYear])
VAR SelectedYearStart = CALCULATE(MIN('DateTable'[Date]), 'DateTable'[FinancialYear] = SelectedYear)
VAR TrendPeriod = DATESBETWEEN(
    'DateTable'[Date],
    DATEADD(SelectedYearStart, -4, YEAR), 
    MAX('DateTable'[Date])
)
RETURN 
    CALCULATE(
        [YourMeasure],
        TrendPeriod
    )

For cards and bar charts, use SelectedYearMeasure to display data only for the selected financial year.

For the trend chart, use TrendMeasure to display data from the last 5 financial years.

I hope this help you, if so, please conssider to mark this reply as solution and give a Kudo.

Thank you!

[Angith_Nair]

Hi @ru_1 

Use a calculated column in your Date table to create a financial year label.

Use the below DAX:

FinancialYear = 
    IF(
        MONTH('Date'[Date]) >= 4,
        YEAR('Date'[Date]) & "-" & YEAR('Date'[Date]) + 1,
        YEAR('Date'[Date]) - 1 & "-" & YEAR('Date'[Date])
    )

Use the below measure for Card and Bar Charts (Selected Year Only):

Measure_SelectedYear = 
    CALCULATE(
        SUM('DataTable'[Value]),
        'Date'[FinancialYear] = SELECTEDVALUE('Date'[FinancialYear])
    )

Use the below measure for Trend Chart (5-Year Range):

Measure_Trend = 
    VAR SelectedYear = SELECTEDVALUE('Date'[FinancialYear])
    VAR StartYear = VALUE(LEFT(SelectedYear, 4)) - 5
    RETURN
        CALCULATE(
            SUM('DataTable'[Value]),
            FILTER(
                ALL('Date'),
                'Date'[FinancialYear] >= StartYear & "-" & StartYear + 1 &&
                'Date'[FinancialYear] <= SelectedYear
            )
        )

Replace the 'DataTable'[Value] with your actual table & column names.

[danextian]

Hi @ru_1 

As always, please provide a workable sample data (not an image) that represents your actual use case and your expected results from that data. This will make easier for the community members to help you. Please see this post: https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...