Can't get the KPI visual to work correctly

[Anonymous]

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?

[Anonymous]

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.

[Anonymous]

It's giving me an error (I'm translating from Dutch): In the True/False expression is no column present. Each True/False expression which is being used as table filter expression, should refer to exactly one column.

[amitchandak]

@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]

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?