Forum Discussion
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 values and cost values into two columns.
I have another table called Calender
Calendar =
ADDCOLUMNS (
CALENDAR (DATE(2000,1,1), DATE(2050,12,31)),
"DateAsInteger", FORMAT ( [Date], "YYYYMMDD" ),
"Year", YEAR ( [Date] ),
"Monthnumber", FORMAT ( [Date], "MM" ),
"YearMonthnumber", FORMAT ( [Date], "YYYY/MM" ),
"YearMonthShort", FORMAT ( [Date], "YYYY/mmm" ),
"MonthNameShort", FORMAT ( [Date], "mmm" ),
"MonthNameLong", FORMAT ( [Date], "mmmm" ),
"DayOfWeekNumber", WEEKDAY ( [Date] ),
"DayOfWeek", FORMAT ( [Date], "dddd" ),
"DayOfWeekShort", FORMAT ( [Date], "ddd" ),
"Quarter", "Q" & FORMAT ( [Date], "Q" ),
"YearQuarter", FORMAT ( [Date], "YYYY" ) & "/Q" & FORMAT ( [Date], "Q" ))
The Revenue is a simple calculation. For the Visual I made RevenueCY
Revenue = SUM(Sales[Revenue])
RevenueCY = CALCULATE([Revenue],'Calendar'[Year] = YEAR(TODAY()))
On the Trend Axis I use Year from the Calender table.
With those two put in the KPI visual as Indicator and Trend I'm getting the Sum of the Revenue of the current year in a black and white visual (no graph or icons). Looks good.
Then I try to add the goal and whatever I put in, it's not showing what I expect. In both cases it does show the RevenueCY, but the Target says: (Empty) (+Infinity%)
RevenueLY = CALCULATE(SUM(Sales[Revenue]) ,YEAR('Calendar'[Year])=YEAR(TODAY()-365))
RevenueLY = CALCULATE([Revenue], SAMEPERIODLASTYEAR('Calendar'[Date]))
I want the target to show the Revenue of the previous year, until the current date (so 1-1-2020 until 11-11-2020), to show whether we're on track compared to last year. Any tips?
4 Replies
- amitchandak
Super User
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
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).
Year RevenueCY RevenuePY 2020 14.92 Mil 2021 14.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?
Hi Anonymous ,
Please have a try.
RevenuePY = CALCULATE(SUM(SALES[Revebue]),EDATE(MAX('Calender'[Year]),-12)).
If it does not help, please provide some data and expected output.
Best Regards
Community Support Team _ Polly
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
