Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Dom87326
Helper II
Helper II

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:

Dom87326_0-1655641169609.png

I want to compare Purchases with Purchases Previous Period in KPI visual, using these attributes:

Dom87326_1-1655641376830.png


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. 

Dom87326_2-1655641572581.png

 

Any help would be appreciated, thanks!
 

2 ACCEPTED SOLUTIONS
aj1973
Community Champion
Community Champion

Hi @Dom87326 

Your Measure must be 

 

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

 

Regards
Amine Jerbi

If I answered your question, please mark this thread as accepted
and you can follow me on
My Website, LinkedIn and Facebook

View solution in original post

aj1973
Community Champion
Community Champion

@Dom87326 

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

aj1973_0-1655752805408.png

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

Regards
Amine Jerbi

If I answered your question, please mark this thread as accepted
and you can follow me on
My Website, LinkedIn and Facebook

View solution in original post

6 REPLIES 6
v-luwang-msft
Community Support
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

aj1973
Community Champion
Community Champion

Hi @Dom87326 

Your Measure must be 

 

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

 

Regards
Amine Jerbi

If I answered your question, please mark this thread as accepted
and you can follow me on
My Website, LinkedIn and Facebook

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.

Dom87326_1-1655647298611.png

Thanks. 

 

aj1973
Community Champion
Community Champion

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

Regards
Amine Jerbi

If I answered your question, please mark this thread as accepted
and you can follow me on
My Website, LinkedIn and Facebook

Hi again @aj1973 ,


Well, I've changed the initial measure to:

Purchases = 
VAR _YM = SELECTEDVALUE('Date'[Year Month])
VAR _CALC = CALCULATE (SUM('Purchases'[Quantity])), 'Date'[Year Month])
RETURN 
_CALC

However KPI still wouldn't return 2022 results.. Maybe I'm doing something wrong here? 

Dom87326_0-1655666222005.png

Thanks!

aj1973
Community Champion
Community Champion

@Dom87326 

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

aj1973_0-1655752805408.png

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

Regards
Amine Jerbi

If I answered your question, please mark this thread as accepted
and you can follow me on
My Website, LinkedIn and Facebook

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors