Forum Discussion

Dom87326's avatar
Dom87326
Helper II
4 years ago
Solved

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!
 

  • Hi Dom87326 

    Your Measure must be 

     

    Previous Purchases = CALCULATE ([Purchases] , SAMEPERIODLASTYEAR('Date'[Date])

     

  • aj1973's avatar
    aj1973
    4 years ago

    Dom87326 

    The filters are applied on the viusal level not the the whole page

    Try to CRTL / SELECT the 3 band chart and watch the card please

6 Replies

  • aj1973's avatar
    aj1973
    Community Champion

    Hi Dom87326 

    Your Measure must be 

     

    Previous Purchases = CALCULATE ([Purchases] , SAMEPERIODLASTYEAR('Date'[Date])

     

    • Dom87326's avatar
      Dom87326
      Helper 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. 

       

      • aj1973's avatar
        aj1973
        Community Champion

        Change the measure using SELECTEDVALUE[YEAR MONTH] as a VAR and CALCULATE the sum of Purchases filtering the selected value .

  • v-luwang-msft's avatar
    v-luwang-msft
    Community 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