Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Measurehelp

This is the example of data i am working on. I want to calculate current year and previous year sale in same row with growth percentage. also i want to use slicer in the report. when i am seleting a current year slicer in my report previous year value is getting blank and also giving growth percent as infinite. I want to see the growth number with the option of slicer in the report

 

Business UnitDateSales
A2/2/2019100
B2/2/2019200
C2/2/2019300
A2/3/2019200
B2/3/2019400
C2/3/2019800
Need Help
  • Ashish_Mathur's avatar
    Ashish_Mathur
    6 years ago

    Hi,

    Try this

    1. Create a Calendar Table and write calculated column formulas to extract Year, Month Name and Month number.  Using the Month number column, use the "Sort By" feature to sort the Month names
    2. Create a relationship from the Date column of your base data table to the Date column of the Calendar Table
    3. Drag Year from the Calendar Table to the column section of the Matrix visual
    4. Write these measures

    Revenue = SUM(Data[Amount])

    Revenue in previuos year = Calculate([Revenue],previousyear(Calendar[Date]))

    Growth in revenue over the previous year = iferror([revenue]/[revenue in previous year]-1,blank())

    Hope this helps.

2 Replies

  • mahoneypat's avatar
    mahoneypat
    Microsoft Employee
    6 years ago

    Try this measure:

     

    YoY Change = var currentyear = selectedvalue(Date[Year])
    var cy = [SalesTotal] // your current measure for sales total

    var py = calculate([SalesTotal], all(Date), Date[Year] = currentyear - 1

    return Divide((cy-py), py) // will protect you from divide by blank

     

    If this works for you, please mark it as the solution.  Kudos are appreciated too.  Please let me know if not.

    Regards,

    Pat

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Super User
    6 years ago

    Hi,

    Try this

    1. Create a Calendar Table and write calculated column formulas to extract Year, Month Name and Month number.  Using the Month number column, use the "Sort By" feature to sort the Month names
    2. Create a relationship from the Date column of your base data table to the Date column of the Calendar Table
    3. Drag Year from the Calendar Table to the column section of the Matrix visual
    4. Write these measures

    Revenue = SUM(Data[Amount])

    Revenue in previuos year = Calculate([Revenue],previousyear(Calendar[Date]))

    Growth in revenue over the previous year = iferror([revenue]/[revenue in previous year]-1,blank())

    Hope this helps.