Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago

Can't get the KPI visual to work correctly

Hi all,   Over the past 2 days I've been eagerly trying to get the KPI visual to work the way I want to.   I have a table with Sales figures from our database and used PQ to split the revenue val...
Need Help
amitchandak's avatar
amitchandak
Icon for Super User rankSuper User
4 years ago

Anonymous , Try like

 

RevenueCY = CALCULATE([Revenue],filter('Calendar', 'Calendar'[Year] = YEAR(TODAY()) ))

 

RevenuePY = CALCULATE([Revenue],filter('Calendar', 'Calendar'[Year] = YEAR(TODAY())-1 ))

 

or

other example

Year behind Sales = CALCULATE(SUM(Sales[Sales Amount]),dateadd('Date'[Date],-1,Year))

 

//Only year vs Year, not a level below

This Year = CALCULATE(sum('Table'[Qty]),filter(ALL('Date'),'Date'[Year]=max('Date'[Year])))
Last Year = CALCULATE(sum('Table'[Qty]),filter(ALL('Date'),'Date'[Year]=max('Date'[Year])-1))

 

 

Make sure calendar is marked as date table

Anonymous's avatar
Anonymous
Not applicable
4 years ago

Thanks for the tips. I used the following suggestions:

RevenueCY = CALCULATE([Revenue],filter('Calendar', 'Calendar'[Year] = YEAR(TODAY()) ))

RevenuePY = CALCULATE([Revenue],filter('Calendar', 'Calendar'[Year] = YEAR(TODAY())-1 ))

 

But two things are not going according to plan.

 

1) In the KPI visual it correctly shows the current Revenue, however the target is still showing (Empty).

When I convert the visual to a table it looks like this, that's probably the reason it fails. It's showing 2021 and compares it to zero (bottomright field).

 

YearRevenueCYRevenuePY
2020 14.92 Mil
202114.12 Mil 

 

2) However the RevenuePY is also incorrect, it shows the total revenue of 2020, not from 1-1-2020 until 11-11-2020.

 

Any additional tips?