Month to Date in KPI

Question & Answer
, ,
1

Hello,
I have this KPI:
image

I want the period to be month-to-date, which means the comparison will be on the previous month’s date.
How can I accomplish that?

Thanks,
Mia

2

Hello @miap, you should be able to utilize your date field for a trend group and set the aggregation to month in order to display this. You can also use a couple of calculated fields to grab the values by month to use in your KPI. If you use dateMonth = truncDate('MM', {date}), you can group dates by each month. Then you can use sumOver({value}, [{dateMonth}], PRE_AGG) to get the total values by month. With a little more information about how your KPI is functioning now and your desired output, I should be able to guide you further.

3

Thank u for the answer,
I’m not sure that it will solve it,
If the date today is 25/12/23 I want to compare my measure from current month to the same previous month to date, which means 01/11/23 - 25/11/23

5

Hello @miap, I believe I have a solution for this. Basically, what you will want to do is creating a field that will group your data. For my example, I am going to create a category for This Month, Last Month, and Other. This will allow you to utilize sumOver based on each group, and then you can just filter out Other.

I created a Date Month field and a Date Day field. Both use truncDate:

Date Month = truncDate('MM', {date})
Date Day = truncDate('DD', {date})

Now use these in an ifelse statement like this:

checkDate = ifelse(
truncDate('MM', now()) = {Order Date Month} AND {Date Day} <= truncDate('DD', now()), 'This Month',
addDateTime(-1, 'MM', truncDate('MM', now())) = {Order Date Month} AND {Date Day} <= addDateTime(-1, 'MM', truncDate('DD', now())), 'Last Month',
'Other'
)

Now, we can use sumOver({value}, [{checkDate}], PRE_AGG) to get the values. You can set up a filter to exclude ‘Other’, and you should get this month to this date and last month to the same day. I’ll mark as a solution but let me know if you have any questions!