Forum Discussion
KPI Visualization does not sum the data YoY
Hello,
I'm trying to compare different time periods with Power BI KPI visuals and for that reason created following measures:
Purchases = SUM('Purchases'[Quantity])
Previous Purchases :=
CALCULATE (
[Purchases],
ALL ( 'Date' ),
USERELATIONSHIP ( 'Date'[Date], 'Previous Date'[Date] )
)
For date table I'm using:
Date =
VAR MinYear = YEAR ( MIN ( 'Purchases'[%DATE] ) ) - 1
VAR MaxYear = YEAR ( MAX ( 'Purchases'[%DATE] ) )
RETURN
ADDCOLUMNS (
FILTER (
CALENDARAUTO ( ),
YEAR ( [Date] ) >= MinYear &&
YEAR ( [Date] ) <= MaxYear
),
"Year", YEAR ( [Date] ),
"Quarter Number", INT ( FORMAT ( [Date], "q" ) ),
"Quarter", "Q" & INT ( FORMAT ( [Date], "q" ) ),
"Month Number", MONTH ( [Date] ),
"Month", FORMAT ( [Date], "mmmm" ),
"Month2", FORMAT ( [Date], "mmm" ),
"Week Day Number", WEEKDAY ( [Date] ),
"Week Day", FORMAT ( [Date], "dddd" ),
"Year Month Number", YEAR ( [Date] ) * 100 + MONTH ( [Date] ),
"Year Month", YEAR ( [Date] ) & " " & FORMAT ( [Date], "mmm" ),
"Year Quarter Number", YEAR ( [Date] ) * 100 + INT ( FORMAT ( [Date], "q" ) ),
"Year Quarter", "Q" & FORMAT ( [Date], "q" ) & "-" & YEAR ( [Date] ),
"Year Month Day Number", (YEAR ( [Date] ) * 100 + MONTH ( [Date] )) * 100 + 01
)
Data model:
I want to compare Purchases with Purchases Previous Period in KPI visual, using these attributes:
However, it seems that when I select more than one month in different year I only see data from the last years months, i.e. only 2022 Jan, 2022 Feb.
Any help would be appreciated, thanks!
6 Replies
- Dom87326Helper II
Hello aj1973 ,
I guess SAMEPERIODLASTYEAR would work if the KPI Value is correct. However in my case, values of KPI doesn't include 2021 Dec result of 147. Although when I use barchart it is included in the visual.In the below example, filter is set on a page level and correct KPI value should be 553 instead of 386.
Thanks.
- aj1973Community Champion
Change the measure using SELECTEDVALUE[YEAR MONTH] as a VAR and CALCULATE the sum of Purchases filtering the selected value .
- v-luwang-msftCommunity Support
Hi Dom87326 ,
Has your problem been solved, if so, please consider Accept a correct reply as the solution or share your own solution to help others find it.
Best Regards
Lucien