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

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
Bassehave
Helper I
Helper I

Calculate a KPI that is always based on last months data

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
1 ACCEPTED SOLUTION
tamerj1
Super User
Super User

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?

 

View solution in original post

8 REPLIES 8
tamerj1
Super User
Super User

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?

 

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!

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

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

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

littlemojopuppy
Community Champion
Community Champion

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!

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.

 

 

 

image.png

image.png

 

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

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()
)

 

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Power BI Header Change

Power BI Header Update

Read about the exciting updates for the Power BI forum.

Top Solution Authors