Forum Discussion

meikev's avatar
meikev
New Member
6 years ago
Solved

replace EndOfYear parameter with column value

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])


Need Help
  • v-lid-msft's avatar
    v-lid-msft
    6 years ago

    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,

     

3 Replies

  • Greg_Deckler's avatar
    Greg_Deckler
    Community Champion
    6 years ago

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

    • meikev's avatar
      meikev
      New Member
      6 years ago

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

       

      • v-lid-msft's avatar
        v-lid-msft
        Community Support
        6 years ago

        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,