Calculate a KPI that is always based on last months data

[Bassehave]

Hello everyone,

I'm trying to do a calculation, but right now it's giving me a blank value.

I have a table called Internal Returns which has a lot of rows with order details. I would like to calculate a KPI that is based on the total amount of return orders that should have been returned last month, with a specific reason code - against the total amount of return orders in the same month (so also last month).

This is what I have been trying to do, so far:

KPI = CALCULATE(DIVIDE(COUNTROWS(FILTER('Internal Returns','Internal Returns'[Return Order Line Status]<>"3950 - Receipt Closed" && 'Internal Returns'[Promised Return Order to Node Date]>= DATE(YEAR(TODAY()), MONTH(TODAY())-1,1)))

, 

COUNTROWS(FILTER('Internal Returns','Internal Returns'[Promised Return Order to Node Date]>=DATE(YEAR(TODAY()),MONTH(TODAY())-1,1)))))

So first one should count all rows that has a date last month (ex. the whole of february) and DONT have the reason code "3950 - Receipt Closed", so everything but this reason code. This result should then be divided by the total amount of return orders last month (all reason codes).

This should give me a percentage of missing returns.

I hope you can support me with this - thanks in advance,

Jesper

[tamerj1 · Accepted solution]

Hi @Bassehave 
You may try the following:

KPI =
VAR _min =
    EOMONTH ( TODAY (), - 2 ) + 1
VAR _max =
    EOMONTH ( TODAY (), - 1 )
RETURN
    DIVIDE (
        COUNTROWS (
            FILTER (
                'Internal Returns',
                'Internal Returns'[Return Order Line Status] <> "3950 - Receipt Closed"
                    && 'Internal Returns'[Promised Return Order to Node Date] >= _min
                    && 'Internal Returns'[Promised Return Order to Node Date] <= _max
            )
        ),
        COUNTROWS (
            FILTER (
                'Internal Returns',
                'Internal Returns'[Promised Return Order to Node Date] >= _min
                    && 'Internal Returns'[Promised Return Order to Node Date] <= _max
            )
        )
    )

Note: I ddid not understand the need for CALCULATE in your code?

[Bassehave]

Hi @tamerj1 ,

Thanks a lot for your help. I have tried to use the code and it does give me some data, but it's not showing the correct number.

I tried to change the formula abit and so far it seems to work with this:

KPI_3 = 
VAR _min =
    EOMONTH ( TODAY (), -2 ) + 1
VAR _max =
    EOMONTH ( TODAY (), -1 )
RETURN
    DIVIDE (
        COUNTROWS (
            FILTER (
                'Internal Returns',
                'Internal Returns'[Return Order Line Status] <> "3950 - Receipt Closed"
                    && 'Internal Returns'[Promised Return Order to Node Date] >= _min
                    && 'Internal Returns'[Promised Return Order to Node Date] <= _max
            )
        ),
        COUNTROWS (
            FILTER (
                'Internal Returns',
                'Internal Returns'[Promised Return Order to Node Date] >= _min
                    && 'Internal Returns'[Promised Return Order to Node Date] <= _max
            )
        )
    )

So now it looks like it's returning all of the data from february.

Can you confirm that changing -1 to -2 in the first parameter, did the job?

Thanks in advance!

[tamerj1]

Yes 100%. I miss understood your requirement. 
With this you will get 1/2/2022 - 28/2/2022

I though you needed 1/3/2022.  - 31/3/2022

[tamerj1]

So do You think my reply is eligible to be markef as accepted solution?

[Bassehave]

I have marked the solution and given you credit - thanks a lot for the help!

[littlemojopuppy]

HI @Bassehave 

Try this...

KPI =
DIVIDE(
	CALCULATE(
		COUNTROWS('Internal Returns'),
		'Internal Returns'[Return Order Line Status] <> "3950 - Receipt Closed",
		PREVIOUSMONTH(DateTable[DateField])
	),
	CALCULATE(
		COUNTROWS('Internal Returns'),
		PREVIOUSMONTH(DateTable[DateField])
	),
	BLANK()
)

Requires that you have a date table and it is marked appropriately.  Make sure you change to the name of your date table and the date field in it.  Hope this helps!

[Bassehave]

I have tried to add a screen shot of the examples.

The filter is the whole of February, so this is the number that I want to show every day of march.

When filtering for february the numbers match. But once I remove the filters the numbers change

If I choose 1 of January to end of february the KPI_2 stays the same (which is what I want) - but if I change the filter to include data after the end of february the data changes.

Is there anyway to lock the data based on the previousmonth - so the data wont change untill we get to April 1st, then the data should reflect the data from 1st of march to the 31 of march.

I hope it makes sense.

FYI - the code you gave (KPI in the middle, it also changes data when we are adding dates from before february, not just after. It seems that it doesnt know which date to look at, but Im not sure.

So based on this, I just need to make it so KPI_2 never looks at data before or after the previous month.

I hope it makes sense.

Thanks in advance,

Jesper

[littlemojopuppy]

Hi @Bassehave 

I'm assuming that when you say "previous month" you mean as of today.  This correct?  If so, try this...

KPI =
DIVIDE(
	CALCULATE(
		COUNTROWS('Internal Returns'),
		'Internal Returns'[Return Order Line Status] <> "3950 - Receipt Closed",
		DATESBETWEEN(
			DateTable[DateField],
			EOMONTH(TODAY(), -2) + 1
			EOMONTH(TODAY(), -1)
		)
			
	),
	CALCULATE(
		COUNTROWS('Internal Returns'),
		DATESBETWEEN(
			DateTable[DateField],
			EOMONTH(TODAY(), -2) + 1
			EOMONTH(TODAY(), -1)
		)	
	),
	BLANK()
)