How to compare current year revenue to previous year in gauge chart.

[swapnil_022]

I have a table with Year and Revenue. I want a gauge which compares current year revenue with last years revenue. Table look like following

 Year       Revenue

 2012       3456788.34

 2013       2345444.54

 2014       2356544.56

 2015       234544.45

 How can I do it in power bi?     

[v-yuezhe-msft]

Hi @swapnil_022,

Firstly, from my point of view, gauge chart is not suitable for your scenario, gauge chart is useful when you want to show progress towards a particular target. You’d better create a column chart or bar chart to show revenue difference between different years.

Secondly, create the following columns in your table.

Index = CALCULATE(COUNT(Table3[Year]),ALL(Table3),FILTER(Table3,Table3[Year]<=EARLIER(Table3[Year])))
Difference = Table3[Revenue]-IF(Table3[Index]=1,Table3[Revenue],LOOKUPVALUE(Table3[Revenue],Table3[Index],Table3[Index]-1))

Thirdly, create a column chart as shown in the following screenshot.

However, if you persist to create a gauge chart, use year slicer to filter the gauge chart.

Regards,
Lydia Zhang

[Phil_Seamark]

Hi @swapnil_022

If you change your Year data to an actual date, eg 2012-01-01 rather than a number you can use the SAMEPERIODLASTYEAR function to create a measure for you.  But when your data is simply 2012 or 2013, Power BI won't know it's a year.

[swapnil_022]

How do I convert text to date type. When I tried changing data type it threw an error