Forum Discussion
KPI Visual - target value
- 3 years ago
Hi Berrcikk
You can use isinscope function to manipulate with the date hierarchies and calculate your goal according to the selections.
Please refer to the attached video :
https://www.youtube.com/watch?v=_p9E8fiDRDwIf this post helps, then please consider Accepting it as the solution to help the other members find it more quickly
- 3 years ago
Hi Berrcikk
You need to apply the needed logic for goals based on the hierarchy filter, if i understood you correctly, on year it should be a year ago, quarter, quarter ago, etc .
So it something like :
test isincscope =
VAR date1 =
MAX ( DimCalendar[Date] )
VAR vMonth =
CALCULATE (
SUM ( vFactSales[Total Sales] ),
datedadd( vFactSales[Date] ,-1,month)
)
VAR vQuarter =
CALCULATE (
SUM ( vFactSales[Total Sales] ),
datedadd( vFactSales[Date] ,-1,quarter)
)
VAR vYear =
CALCULATE (
SUM ( vFactSales[Total Sales] ),
datedadd ( vFactSales[Date] ,-1,Year)
)
VAR scope1 =
SWITCH (
TRUE (),
ISINSCOPE ( DimCalendar[Date].[Month] ), vMonth,
ISINSCOPE ( DimCalendar[Date].[Month] ), vQuarter,
ISINSCOPE ( DimCalendar[Date].[Month] ), vYear,
1
)
RETURN
scope1
Ritaf1983 Sure, I hope this format will be fine.
So fact table looks more or less like that.
| sales value | date | car type |
| 10 | 04.10.2020 | A |
| 25 | 19.09.2020 | A |
| 32 | 17.08.2020 | B |
| 14 | 14.06.2020 | B |
Now, let's assume we have a slicer date, and a clustered chart with sales on Y axis, and date on X axis.
Based on what granularity on chart is shown (ex. years, months, quarters), KPI card should display value for current month, and target for previous one. So for screen below, it should be 548 and 535.
However, if I change granularity to quarters, as on the screen below, it should present 1.3k, and 1k.
- Ritaf19833 years ago
Super User
Hi Berrcikk
You can use isinscope function to manipulate with the date hierarchies and calculate your goal according to the selections.
Please refer to the attached video :
https://www.youtube.com/watch?v=_p9E8fiDRDwIf this post helps, then please consider Accepting it as the solution to help the other members find it more quickly