0

No products in the cart.

0

No products in the cart.

Home
Blog Standard
KPI
Power BI KPI Card part-2
KPI to show year-on-year % change
  • A KPI card with year-on-year (YoY) percentage change is a valuable tool for tracking how a key metric (e.g., sales, revenue, profit) has changed compared to the same period in the previous year. It provides a quick snapshot of performance growth or decline over
    time.

  • This type of visualization is crucial for identifying trends and assessing business
    performance over time, enabling decision-makers to take informed action based on datadriven insights.

    • EXPLANATION

    This is the main KPI value, such as sales or profit for the current year or period.

    The reference point for comparison, typically the metric’s value for the same period in the previous year.

    This shows how much the KPI has changed as a percentage compared to the previous year. It indicates whether performance has improved, stayed the same, or declined.

    STEP 1: Create measures for current year and pre year sales

    Create two measures for getting maximum year from a disconnected table to get the current year and as well previous year
    Year filter = Max('Year'[Year])
Pre Year = [Year filter]-1
    Create a total sales measure, Sales = Sum(Data[Sales])
    Sales = SUM(Data[Sales])
    Create two measures to calculate current year and pre-year sales
    Sales CY = CALCULATE([Sales], FILTER('Date', 'Date'[Year] =
FORMAT([Year filter],"####")))
Sales CY-1 = CALCULATE([Sales], FILTER('Date', 'Date'[Year] =
FORMAT([Pre Year],"####")))

    STEP 2: Create measures to pre year and YoY%

    Create measures to calculate pre-year sales Sales PY =
    CALCULATE([Sales], SAMEPERIODLASTYEAR( 'Date'[Date]))
    Create measures to calculate YoY% change Sales YoY% =
    DIVIDE([Sales] - [Sales PY], [Sales PY],0)
    Create measures to % change for current and previous year Sales CY vs CY-1% =
    DIVIDE([Sales CY] - [Sales CY-1], [Sales CY-1],0)

    STEP 3: Create measures to format based on values

    To display the target achieved label in appropriate color Red or Green
    TargetCY Color = IF([Sales CY]>[Target CY], "green", "red")
    Arrow up & down 
    _Arrow down = UNICHAR(9660)
_Arrow up = UNICHAR(9650)
    Format in Millions,
     _Format = "$#0,,.00M;-$#0,,.00M«
    TargetCY ? =
    "Target " & IF([Sales CY]>[Target CY], "Achieved","Missed")
    To display sales pre year label dynamically
    Sales Pre Year = "Sales " & [Pre Year]
    To display sales YoY on different color
    Sales YoY Color = IF([Sales]>[Sales PY], "green", "red")

    STEP 4: Create measures to hold dynamic labels for pre-year sales vs current year

    Sales Growth CY vs CY-1 =
    VAR _Value = FORMAT([Sales CY-1], [_Format])
VAR _ValueChange = FORMAT([Sales CY vs CY-1%], "#%")
VAR _FormattedText =
_Value & IF(
[Sales CY vs CY-1%] <=0,
" | " & [_Arrow down] & _ValueChange ,
" | " & [_Arrow up] & "+" & _ValueChange )
VAR _Result=
IF ( OR(ISBLANK([Sales CY vs CY-1%]), [Sales CY vs CY-1%]=0) , _Value, if(
[Sales CY vs CY-1%]=0, BLANK(), _FormattedText ) )
RETURN _Result

    STEP 5: Create measures to hold dynamic labels for current year sales target achieved or not

    TargetCY Achieved =
    VAR _Value = FORMAT([Target CY], [_Format])
VAR _ValueChange = FORMAT([Target CY%], "#%")
VAR _FormattedText =
_Value & IF(
[Target CY%] <=0,
" | " & [_Arrow down] & _ValueChange ,
" | " & [_Arrow up] & "+" & _ValueChange )
VAR _Result=
IF ( OR(ISBLANK([Target CY%]), [Target CY%]=0) , _Value, if( [Target CY%]=0, BLANK(), _FormattedText
) )
RETURN _Result
    en_USEnglish
    en_USEnglish