cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
dbrandone
Helper III
Helper III

YTD and Last Year Percentages coming back the same

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
 
1 ACCEPTED SOLUTION
yingyinr
Community Support
Community Support

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

Community Support Team _ Rena
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

3 REPLIES 3
yingyinr
Community Support
Community Support

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

Community Support Team _ Rena
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

amitchandak
Super User IV
Super User IV

@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



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

@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?

Helpful resources

Announcements
PBI_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

Get Ready for Power BI Dev Camp

Microsoft named a Leader in The Forrester Wave

Microsoft received the highest score of any vendor in both the strategy and current offering categories.

Get Ready for Power BI Dev Camp

Power BI Dev Camp - September 30th, 2021

Mark your calendars and join us for our next Power BI Dev Camp!

PowerPlatform 768x460.png

Microsoft Learn

Check out our new Discover Your Career Path blog post series and get all the details.

Top Solution Authors