KPI Calculation for Last Available Month Based on Dynamic Date Range

Question & Answer
, , ,
1

Hello all,

I have a KPI which should show Total Sum of Findings for Last Month.

I have two parameters in my control called Start Date and End Date. Start Date is start of last 12 month and End Date is End of this Month.

  • If I select start date as June 2024 and End Date as August 2024, my KPI should give me sum of findings for August 2024.

  • If I select Start date as June 2024 and End Date as March 2025, KPI should reflect February 2025 data because that is the max or the latest data available.

I also have a Month field. I initially tried to add this month field as a trend group in my KPI and it was working well but I don’t want any dates to be displayed under this KPI. I just need sum of findings displayed and underneath I need a text field like “Total Sum for Last Month”. So I thought I can only achieve this if I add a calculated field.

I tried this calculation:

Findings =

Ifelse(
Truncdate(“MM”, Month) = truncdate(“MM”, addDatetime(0,’MM’, truncdate(“MM”, End Date))), Findings, null)

This calculation is only working when I select start date as June 2024 and End Date as August 2024, this will give me August 2024 data.

But when I select June 2024 to March 2025, I expect it to show Feb data as its the max or latest data available but it gives me no data.

I see two solutions here:

Either add the Month field under trend group and it will dynamically grab the max date available. The only problem with this is that the KPI will also display the month while I don’t want it.

The other one is maybe creating a calculated field like I did. But somehow this calculation is not working.

Please let me know how to improve my logic/calculation here or if there is any simplest way to do it. Thanks.

2

Hey @Surs

In this case does your dataset have data for March 2025 or is that data going to be updated/this KPI is specifically for showing last month data?

If you have data in March 2025 and don’t want to show it you could try:

ifelse(
	extract('YYYY', ${EndDate}) = truncDate('YYYY', now()),
		ifelse(
			truncDate('MM',{Order Date}) = addDateTime(-1,'MM',truncDate('MM',${EndDate})),
			{Sales},
			Null
		),
	extract('YYYY', ${EndDate}) <> truncDate('YYYY', now()),
		ifelse(
			truncDate('MM',{Order Date})= truncDate('MM',${EndDate}),
			{Sales},
			Null
		),
	NULL
)

If you don’t have data in the max month of your dataset you can try:

ifelse(
truncDate("MM", ${EndDate}) = truncDate("MM", {Order Date}) AND (isNull({Sales}) OR {Sales} = 0, addDateTime(-1, "MM", {OrderDate},
truncDate("MM", ${EndDate}) = truncDate("MM", {Order Date}) AND {Sales} > 0, 
{OrderDate}, 
NULL)