KPI Card data

[Naveen29]

I would like to show data in the KPI card as below. when user selects current year as 2024 from slicer, it should show sum of sales for groups A,B,C channels and same as for previous year (2023) as well. But when user selects as 2023 from slicer , it should show only group of A,B .How do we achive this?

[rajendraongole1 · Accepted solution]

Hi @Naveen29 Create a two measure for current year sales and previous year sales as below

SalesCurrentYear =
VAR SelectedYear = SELECTEDVALUE('Date'[Year])
RETURN
IF(
SelectedYear = 2024,
CALCULATE(
SUM('Sales Data'[SalesAmount]),
'Sales Data'[Channel] IN {"A", "B", "C"},
YEAR('Sales Data'[Date]) = 2024
),
IF(
SelectedYear = 2023,
CALCULATE(
SUM('Sales Data'[SalesAmount]),
'Sales Data'[Channel] IN {"A", "B"},
YEAR('Sales Data'[Date]) = 2023
),
BLANK()
)
)

Create another measure for Previous Year

SalesPreviousYear =
VAR SelectedYear = SELECTEDVALUE('Date'[Year])
RETURN
IF(
SelectedYear = 2024,
CALCULATE(
SUM('Sales Data'[SalesAmount]),
'Sales Data'[Channel] IN {"A", "B", "C"},
YEAR('Sales Data'[Date]) = 2023
),
IF(
SelectedYear = 2023,
CALCULATE(
SUM('Sales Data'[SalesAmount]),
'Sales Data'[Channel] IN {"A", "B"},
YEAR('Sales Data'[Date]) = 2022
),
BLANK()
)
)

Create a KPI card visualization for current year and previous year

Set the Indicator to SalesCurrentYear the  measure.Set the Target if needed, or empty. do the same for another KPI card visual for SalesPreviousYear measure.

set the slicer to filter on the year column from date table.

Hope it works 

Did I answer your question? Mark my post as a solution! This will help others on the forum!
Appreciate your Kudos!!

[Ritaf1983 · Accepted solution]

Hi @Naveen29 
You can create 2 measures like :

Current total sales = if(max('Calendar'[Year])= 2024,

 CALCULATE([total_sales],'orders'[group] IN {"A","B","C"}),

IF(MAX('Calendar'[Year])=2023 ,CALCULATE([total_sales],'orders'[group] IN {"A","B"})))

previ year = if(max('Calendar'[Year])= 2024,

 CALCULATE([total_sales],SAMEPERIODLASTYEAR('Calendar'[Date]),'orders'[group] IN {"A","B","C"}),

IF(MAX('Calendar'[Year])=2023 ,CALCULATE([total_sales],SAMEPERIODLASTYEAR('Calendar'[Date]),'orders'[group] IN {"A","B"})))

Result:

The pbix is attached

If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly.

[Anonymous · Accepted solution]

Thanks for the reply from @rajendraongole1 and@Ritaf1983, please allow me to provide another insight:
Hi,@Naveen29 

Regarding the issue you raised, my solution is as follows:

1.First I have created the following table and the column names and data are the data you have given:

2.I created the following calculation column as a slicer:

YEARSSS = SELECTCOLUMNS('MAIN',"1",'MAIN'[YEAR])

3. Below are the measure I've created for your needs:

CURRENT = 
VAR SELECTYEAR=SELECTEDVALUE(YEARSSS[YEAR])
VAR PREYEAR=SELECTYEAR-1
RETURN 
IF(ISFILTERED(YEARSSS[YEAR]),SWITCH(TRUE(),
SELECTYEAR=2024,CALCULATE(SUM(MAIN[SALES]),FILTER(ALL('MAIN'),'MAIN'[YEAR] IN {PREYEAR}&&'MAIN'[channel] IN {"A","B","C"})),
SELECTYEAR=2023,CALCULATE(SUM(MAIN[SALES]),FILTER(ALL('MAIN'),'MAIN'[YEAR] IN {PREYEAR}&&'MAIN'[channel] IN {"A","B"})),FALSE())
,SUM(MAIN[SALES]))

LAST = 
VAR SELECTYEAR=SELECTEDVALUE(YEARSSS[YEAR])
RETURN 
IF(ISFILTERED(YEARSSS[YEAR]),SWITCH(TRUE(),
SELECTYEAR=2024,CALCULATE(SUM(MAIN[SALES]),FILTER(ALL('MAIN'),'MAIN'[YEAR] IN {SELECTYEAR}&&'MAIN'[channel] IN {"A","B","C"})),
SELECTYEAR=2023,CALCULATE(SUM(MAIN[SALES]),FILTER(ALL('MAIN'),'MAIN'[YEAR] IN {SELECTYEAR}&&'MAIN'[channel] IN {"A","B"})),FALSE())
,SUM(MAIN[SALES]))

4.Here's my final result, which I hope meets your requirements.

Can you share sample data and sample output in tabular format if I am misunderstanding? Or a sample pbix after removing sensitive data. We can better understand the problem and help you.
Please find the attached pbix relevant to the case.

Best Regards,

Leroy Lu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Ritaf1983 · Accepted solution]

Hi @Naveen29 
You can create 2 measures like :

Current total sales = if(max('Calendar'[Year])= 2024,

 CALCULATE([total_sales],'orders'[group] IN {"A","B","C"}),

IF(MAX('Calendar'[Year])=2023 ,CALCULATE([total_sales],'orders'[group] IN {"A","B"})))

previ year = if(max('Calendar'[Year])= 2024,

 CALCULATE([total_sales],SAMEPERIODLASTYEAR('Calendar'[Date]),'orders'[group] IN {"A","B","C"}),

IF(MAX('Calendar'[Year])=2023 ,CALCULATE([total_sales],SAMEPERIODLASTYEAR('Calendar'[Date]),'orders'[group] IN {"A","B"})))

Result:

The pbix is attached

If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly.

[rajendraongole1 · Accepted solution]

Hi @Naveen29 Create a two measure for current year sales and previous year sales as below

SalesCurrentYear =
VAR SelectedYear = SELECTEDVALUE('Date'[Year])
RETURN
IF(
SelectedYear = 2024,
CALCULATE(
SUM('Sales Data'[SalesAmount]),
'Sales Data'[Channel] IN {"A", "B", "C"},
YEAR('Sales Data'[Date]) = 2024
),
IF(
SelectedYear = 2023,
CALCULATE(
SUM('Sales Data'[SalesAmount]),
'Sales Data'[Channel] IN {"A", "B"},
YEAR('Sales Data'[Date]) = 2023
),
BLANK()
)
)

Create another measure for Previous Year

SalesPreviousYear =
VAR SelectedYear = SELECTEDVALUE('Date'[Year])
RETURN
IF(
SelectedYear = 2024,
CALCULATE(
SUM('Sales Data'[SalesAmount]),
'Sales Data'[Channel] IN {"A", "B", "C"},
YEAR('Sales Data'[Date]) = 2023
),
IF(
SelectedYear = 2023,
CALCULATE(
SUM('Sales Data'[SalesAmount]),
'Sales Data'[Channel] IN {"A", "B"},
YEAR('Sales Data'[Date]) = 2022
),
BLANK()
)
)

Create a KPI card visualization for current year and previous year

Set the Indicator to SalesCurrentYear the  measure.Set the Target if needed, or empty. do the same for another KPI card visual for SalesPreviousYear measure.

set the slicer to filter on the year column from date table.

Hope it works 

Did I answer your question? Mark my post as a solution! This will help others on the forum!
Appreciate your Kudos!!