Need help with creating a measure

[kjm5200]

Hi there.. I am hoping someone would be able to help me with creating a measure. I have a data set of 300k+ records with information related to client applications for new accounts with my firm.  What I am trying to compute is the amount of approved unfunded applications in the previous 60 days and I want to see this information on a daily graph.  Below is an example of the data.  So for example on 6/1 we would want to count the bold record below in the data set since it funded on 6/2. Can anyone help?  Thanks you!

Application Date	Fund Date	Approved
4/25/2017		No
1/7/2017		No
1/13/2017		No
7/10/2017		No
7/22/2017		Yes
5/8/2017	6/2/2017	Yes
4/27/2017	5/3/2017	Yes
6/24/2017		Yes
7/11/2017	7/14/2017	Yes
7/18/2017		Yes
5/22/2017	6/30/2017	Yes
5/31/2017	6/8/2017	Yes
6/1/2017	6/13/2017	Yes
6/14/2017		Yes
2/9/2017	2/22/2017	Yes
3/9/2017	4/22/2017	Yes
3/23/2017	3/24/2017	Yes
4/4/2017	4/18/2017	Yes
5/1/2017	5/4/2017	Yes
7/21/2017		Yes
4/7/2017	4/12/2017	Yes
4/28/2017	5/19/2017	Yes
2/16/2017		Yes
2/19/2017	3/7/2017	Yes
1/2/2017	1/7/2017	Yes
4/12/2017	4/25/2017	Yes
3/27/2017	4/4/2017	Yes
4/4/2017		Yes
1/9/2017		Yes
4/24/2017		Yes
7/10/2017		Yes
3/16/2017		No

[Anonymous]

Easiest way to do this would be to split it into 2 tasks:

Task 1:  Is the given record in the last 60 days?

Create a custom column that is a simple True/False.  This could created in your Date table if you are doing this.  If not, in the records table instead.

The column would look like:

Last60Days = IF(
	TODAY() - 'YourTable'[YourDate] <= 60,
	TRUE(),
	FALSE()
)

So now, you can place this field into your filters and only display when this is TRUE.

TASK 2:  Is a record Approved and Unfunded?

For this one, i'd use a calculate statement that will cut down what is being calculated by a Measure, which will calculate your number of approved & unfunded:

CountApprovedUnfunded = CALCULATE(
	COUNTROWS('YourTable'),
	'YourTable'[Approved} = "Yes",
	ISBLANK('YourTable'[Fund Date])
)

Here i've assumed that if the fund date is blank, that it is unfunded.  If the Approved column is 'Yes', i've assumed its approved.  Thus combined, that record must be approved and unfunded.

[kjm5200]

Hi Ross,

Thank you for the reponse.  If I am understanding you correctly that will provide the results at a specific point in time (i.e today).  What i am trying to do is compare today against historical values.  So for example i would want to see a day by day view for all of of July so i can pinpoint any trends.  So if historcally on average we have 1000 unfunded applications and then i notice that starts to increase to 1500 there may be an issue.  Below is an example of what the results would look like.

Date	Unfunded Applications Last 60 days
07/01/17	1000
07/02/17	1005
07/03/17	995
07/04/17	975
07/05/17	995
07/06/17	1002
07/07/17	1075
07/08/17	1050
07/09/17	1065
07/10/17	1010
07/11/17	999
07/12/17	1000
07/13/17	1012
07/14/17	1042
07/15/17	1030
07/16/17	1040
07/17/17	1021

Thanks,

Kevin

[Anonymous · Accepted solution]

Ok, we can do this.  I'm just going to get a lot more complex. So i hope i don't lose you.  This will require 3 tasks:

1.  You will need a Date Table, which will need to be linked to your table you are reporting on.  If you dont have a date table, you can create one using this guide:
https://www.agilebi.com.au/blog/power-bi-date-dimension

2.  Create a measure that produces the number you want, except it does all dates.  I.e. something like "= Sum('Your Table'[Your Value].  For the purpose of my example in (3), i'll call this [YourMeasure]

3.  Now create this measure:

YourResult = 
VAR LineDate = 'Your Table'[Date]
RETURN

CALCULATE( 	
	SUMX(
		values('Dim - Date Table'[Date]), 
		[EBIT]
	),
	DATESINPERIOD(
		'Dim - Date Table'[Date], 
		LineDate, 
		-60, 
		DAY
	)
)

Let me know if there are any problems with this measure.  I knocked it together fairly quickly.

[kjm5200]

It took me a while to figure a couple things out but once i got my head on straight it worked!!!  Thank you!!!