Matrix - YoY percentage is displayed only on a few values

[themistoklis]

Hello All,

I have the following Matrix on PowerBI and when i use the formula for the YoY calculation i get blank for "Cancelled" and "Lost" although there are numbers in both cells for 2018 YTD and 2017 YTD. I get the percentage for the first 'Status' value only.

The formula im using is the following:

YoY % Diff = DIVIDE(([NetValue_YTD_TY_Quo] - [NetValue_YTD_PY_QuoWon]), [NetValue_YTD_PY_QuoWon])

NetValue_YTD_TY_Quo = 
IF (
    MIN ( 'Calendar'[Date] ) <= CALCULATE ( MAX ( 'Master Table'[Reference_Date]), ALL ( 'Master Table' ) ),
    CALCULATE ( SUM(Quotations[Net_Value_Quo]), DATESYTD ( 'Calendar'[Date] ) )
)

NetValue_YTD_PY_Quo = VAR DataMaxDate =
    CALCULATE ( MAX ( 'Quotations'[Reference_Date] ), ALL ( 'Quotations' ) )
RETURN
    CALCULATE (
        [NetValue_YTD_TY_Quo],
        SAMEPERIODLASTYEAR (
            INTERSECT (
                VALUES ( 'Calendar'[Date] ),
                DATESBETWEEN ( 'Calendar'[Date], BLANK (), DataMaxDate )
            )
        )
    )

Any idea why im getting only the percentage difference for a few "Status" values?

Thanks

[Ashish_Mathur]

Hi,

I'm not sure but try these simplified measures

NetValue_YTD_TY_Quo = CALCULATE(SUM(Quotations[Net_Value_Quo]), DATESYTD('Calendar'[Date]))

NetValue_YTD_PY_Quo = CALCULATE([NetValue_YTD_TY_Quo], SAMEPERIODLASTYEAR('Calendar'[Date]))

YoY % Diff = DIVIDE(([NetValue_YTD_TY_Quo] - [NetValue_YTD_PY_QuoWon]), [NetValue_YTD_PY_QuoWon])

Ensure that you select a specific month in the slicer/filter

[themistoklis · Accepted solution]

@Ashish_Mathur

Ashish thank you for the reply. Your formulas are more compact and more readable but i still got the samem proble with the YoY % diff.

I found out that the following formula did the trick:

YoY % Diff = DIVIDE((IF(ISBLANK([NetValue_YTD_TY_Quo]),0,[NetValue_YTD_TY_Quo]) - IF(ISBLANK([NetValue_YTD_PY_Quo]),0,[NetValue_YTD_PY_Quo])),IF(ISBLANK([NetValue_YTD_PY_Quo]),0,[NetValue_YTD_PY_Quo]))