Target measure sums targets for 2 FY's when week stretches over FY

[SteveCarter1]

Hi

I have a problem when viewing a chart in Start of Week X-Axis where my target measure (the yellow line in the chart below) is suming targets that span over 2 financial years but only for the single week where it crosses over a financial year boundary.

For example Sunday, Monday and Tuesday are in FY 2021-2022 and Wednesday, Thursday, Friday and Saturday are in FY 2022-2023

I have some targets against different services in an excel file where I specify the Financial Year they apply:

Service	Target	Financial Year
A	100	2021-2022
B	110	2021-2022
C	120	2021-2022
D	130	2021-2022
E	140	2021-2022
A	101	2022-2023
B	111	2022-2023
C	121	2022-2023
D	131	2022-2023
E	141	2022-2023

I have a master calendar table that labels every day with its Financial Year then have a relationship between those 2 tables which is how I link the FY tag to a date.

This is my simple measure to show the target line:

FYClientTarget = 
                IF( ISBLANK(
                        SUM(
                            'Client Targets'[Target]
                        )
                    ),
                    BLANK(),
                    SUM(
                         'Client Targets'[Target]
                    )
                )

I understand what is happening but not how to fix it.

The SUM function is seeing for that 1 week, there are 2 targets for each service - one for 2021-2022 and another for 2022-2023 because there are days that fall into both financial years so it is adding up both targets for all services.

I think I need to find some way to do a group or distinct or even a min type of function or filter to end up with only ever 1 target for any given X-Axis level (in this case per week) and ideally the target value that should be used is the one that applies to the first date in the X-Axis group (e.g. the first day of that week only when viewing using week X-Axis).

All other X-Axis views are fine because they don't span over the financial year boundry, such as month, quarter, year etc.

Any ideas how I can sort this out?

[johnt75 · Accepted solution]

Try

FY Client Target =
VAR minDate =
    MIN ( 'Date'[Date] )
VAR FY =
    LOOKUPVALUE ( 'Date'[FY], 'Date'[Date], minDate )
VAR target =
    CALCULATE ( SUM ( 'Client Targets'[Target] ), 'Date'[FY] = FY )
RETURN
    IF ( NOT ISBLANK ( target ), target )

[SteveCarter1]

Thanks! A bit of a ah yeah moment.  Makes sense to get the minimum date in the week, lookup what financial year that is, then do the sum on the target table only where dates match that financial year.