YTD and Last Year Percentages coming back the same

[dbrandone]

Hi all, 

I had last year and YTD percentage cards come back the same percentage. I then realized based on my data that I needed additional filters. I updated the Last year dax, but cannot get the DAX to work with filters and previous year. 

Last Year Transplant Rate =

         VAR LastYear =

                PREVIOUSYEAR('Date Table'[Date], "12/31")

         VAR Numerator =

             CALCULATE(COUNT(vTransplantRate[TissueOutcome]), FILTER(vTransplantRate, LastYear),                                                                   vTransplantRate[TissueOutcome] = "Transplant")

        VAR Denominator =

             CALCULATE(COUNT(vTransplantRate[RecoveryIntent]), FILTER(vTransplantRate, LastYear),

             vTransplantRate[RecoveryIntent]  = "Transplant", IF(NOT(ISBLANK(vTransplantRate[TissueOutcome])),                                                 vTransplantRate[TissueOutcome]))

     VAR Result =

            DIVIDE(Numerator, Denominator)

      Return

            Result

I also have included my YTD Dax that I will also need to add in similar filters to the YTD measure to update it:

YTD Transplant Rate =

    VAR vToday =

        TODAY ()

    VAR vStartDate =

        STARTOFYEAR(vTransplantRate[PreservationOn].[Date], "12/31")

    VAR vNumerator =

        CALCULATE (

            vTransplantRate[TransplantRate Count],

            vTransplantRate[TissueOutcome] = "Transplant",

            DATESBETWEEN ( 'Date Table'[Date], vStartDate, vToday )

                          )

    VAR vDenominator =

        CALCULATE ( vTransplantRate[TransplantRate Count], DATESBETWEEN ( 'Date Table'[Date], vStartDate, vToday ) )

    VAR vResult =

        DIVIDE ( vNumerator, vDenominator )

    RETURN

        vResult

[v-yiruan-msft · Accepted solution]

Hi @dbrandone ,

First, please make sure that there is one relationship has been created between Date Table [Date] and vTransplantRate[PreservationOn]. Then please update the formula of measures [YTD Transplant Rate] and [Last Year Transplant Rate] as below separately:

YTD Transplant Rate =
VAR vNumerator =
    CALCULATE (
        vTransplantRate[TransplantRate Count],
        vTransplantRate[TissueOutcome] = "Transplant",
        DATESYTD ( 'Date Table'[Date] )
    )
VAR vDenominator =
    CALCULATE (
        vTransplantRate[TransplantRate Count],
        DATESYTD ( 'Date Table'[Date] )
    )
VAR vResult =
    DIVIDE ( vNumerator, vDenominator )
RETURN
    vResult

Last Year Transplant Rate =
VAR Numerator =
    CALCULATE (
        COUNT ( vTransplantRate[TissueOutcome] ),
        vTransplantRate[TissueOutcome] = "Transplant",
        DATEADD ( DATESYTD ( 'Date Table'[Date] ), -1, YEAR )
    )
VAR Denominator =
    CALCULATE (
        COUNT ( vTransplantRate[RecoveryIntent] ),
        vTransplantRate[RecoveryIntent] = "Transplant",
        IF (
            NOT ( ISBLANK ( vTransplantRate[TissueOutcome] ) ),
            vTransplantRate[TissueOutcome]
        ),
        DATEADD ( DATESYTD ( 'Date Table'[Date] ), -1, YEAR )
    )
VAR Result =
    DIVIDE ( Numerator, Denominator )
RETURN
    Result

If the above ones can't return the correct result, please provide the related calculation logic and more details for your requirement. Thank you.

Best Regards

[amitchandak]

@dbrandone , Try this change and check

Last Year Transplant Rate =
VAR LastYear =
PREVIOUSYEAR('Date Table'[Date], "12/31")
VAR Numerator =
CALCULATE(COUNT(vTransplantRate[TissueOutcome]),  LastYear) vTransplantRate[TissueOutcome] = "Transplant")
VAR Denominator =
CALCULATE(COUNT(vTransplantRate[RecoveryIntent]),  LastYear,
vTransplantRate[RecoveryIntent] = "Transplant", IF(NOT(ISBLANK(vTransplantRate[TissueOutcome])), vTransplantRate[TissueOutcome]))
VAR Result =
DIVIDE(Numerator, Denominator)
Return
Result

[dbrandone]

@amitchandak I get a "Couldn't load the data for this visual" error.

Error Message:
MdxScript(Model) (146, 61) Calculation error in measure 'iTx Measures'[vTR_Last Year Transplant Rate]: Cannot convert value 'Transplant' of type Text to type True/False.

Would this be a time where we would need to designate "transplant" as a value of 1, else 0 and then run a count? If so, how would I go about implementing that into the DAX?