replace EndOfYear parameter with column value

[meikev]

Spoiler

Trying to update the TotalTYD function parameter for YearEndDate with a value from another column (that can be updated by the user).

YTD WIP Total Fees = TOTALYTD(SUM(Perspectives[WIP Total Fees]),'date'[date],"31 Mar")

want to replace the 31 Mar with a column that a user can then update for their own requirements

YTD WIP Total Fees = TOTALYTD(SUM(Perspectives[WIP Total Fees]),'date'[date],Table[EOY])

[Greg_Deckler]

Genrally, you would use a What If parameter and associated measure to do that.

[meikev]

a What-if parmater can only be numeric (whole/decimal) - this function requires a string argument that equates to a date

[v-lid-msft · Accepted solution]

Hi @meikev ,

We can try to use a calculated table and a measure to meet your requirement:

Calculated table:

Table = ADDCOLUMNS ( CALENDARAUTO (), "EOY", FORMAT ( [Date], "DD MMM" ) )

Measure:

YTD WIP Total Fees =
VAR endDate =
    MAX ( 'Perspectives'[Date] )
VAR startDate =
    CALCULATE ( MAX ( 'Table'[Date] ), 'Table'[Date] <= endDate )
RETURN
    CALCULATE (
        SUM ( 'Perspectives'[WIP Total Fees] ),
        FILTER (
            ALLSELECTED ( 'Perspectives' ),
            'Perspectives'[Date] >= startDate
                && 'Perspectives'[Date] <= endDate
        )
    )

If it doesn't meet your requirement, Could you please show the exact expected result based on the Tables that we have shared？

By the way, PBIX file as attached.

Best regards,