Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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 |
Solved! Go to 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.
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.
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
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.
It took me a while to figure a couple things out but once i got my head on straight it worked!!! Thank you!!!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
109 | |
96 | |
77 | |
66 | |
53 |
User | Count |
---|---|
144 | |
105 | |
102 | |
89 | |
63 |