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 All,
I'm looking for some help in calculating an end date for the image below OR for a way to determine the correct fee amount based on the course_fee_id being the same AND the start date of the course (linked table), being greater than or equal to the effective date, but less than the next effective date.
The source date/fee data is below. The table that needs to call on the fee amount based on fee_id and Effective_date is a separate table.
Thanks!
Solved! Go to Solution.
hi, @cdn4lf
You could use this formula to add a Effective_End_Date column in table 1, then create another column in table2
Effective_End_Date = IF ( CALCULATE ( MIN ( Table1[Effective_Start_Date] ), FILTER ( Table1, Table1[Fee_ID] = EARLIER ( Table1[Fee_ID] ) && Table1[Effective_Start_Date] > EARLIER ( Table1[Effective_Start_Date] ) ) ) = BLANK (), DATE ( 9999, 12, 31 ), CALCULATE ( MIN ( Table1[Effective_Start_Date] ), FILTER ( Table1, Table1[Fee_ID] = EARLIER ( Table1[Fee_ID] ) && Table1[Effective_Start_Date] > EARLIER ( Table1[Effective_Start_Date] ) ) ) - 1 )
Best regards,
Lin
hi, @cdn4lf
You may need to use "all" function in your measure
for the start date of the course (linked table), being greater than or equal to the effective date, but less than the next effective date.
We could get the information from screenshot, so please share a simple complete sample data and expected output.
Best Regards,
Lin
Hello,
Please see below. I've provided two possible outputs, that should both do what I need. Table #1 and Table #2, are linked by the Fee_ID and linked across, such that the fee used matches the Fee_ID and the effective date is less than the start date, BUT, also less than the next effective date for that Fee_ID.
Fee_ID | Effective Date | Fee Amount |
1 | 2002/01/01 | 0 |
1 | 2003/01/16 | 0 |
1 | 2004/01/31 | 0 |
1 | 2005/02/14 | 0 |
1 | 2006/03/01 | 1 |
1 | 2007/03/16 | 1 |
1 | 2008/03/30 | 1 |
2 | 2002/01/02 | 45 |
2 | 2003/01/17 | 35 |
2 | 2004/02/01 | 48 |
2 | 2005/02/15 | 49 |
2 | 2006/03/02 | 60 |
2 | 2007/03/17 | 55 |
2 | 2008/03/31 | 58.75 |
2 | 2002/01/03 | 62 |
3 | 2003/01/18 | 12 |
3 | 2004/02/02 | 13 |
3 | 2005/02/16 | 14 |
3 | 2006/03/03 | 15 |
3 | 2007/03/18 | 19 |
3 | 2008/04/01 | 22 |
3 | 2002/01/04 | 22.75 |
3 | 2003/01/19 | 38 |
4 | 2004/02/03 | 150 |
4 | 2005/02/17 | 151 |
4 | 2006/03/04 | 156 |
4 | 2007/03/19 | 175 |
4 | 2008/04/02 | 180 |
4 | 2002/01/05 | 200 |
4 | 2003/01/20 | 195 |
Course_ID | Fee_ID | Start_Date |
1 | 1 | 2002/02/20 |
2 | 2 | 2002/04/02 |
3 | 3 | 2003/04/03 |
4 | 4 | 2004/04/03 |
5 | 1 | 2006/04/20 |
6 | 2 | 2006/05/31 |
7 | 3 | 2007/06/01 |
8 | 4 | 2008/06/01 |
9 | 1 | 2004/03/21 |
10 | 2 | 2002/04/03 |
11 | 3 | 2003/04/04 |
12 | 4 | 2003/03/21 |
13 | 1 | 2008/05/19 |
14 | 2 | 2005/05/16 |
15 | 3 | 2005/05/02 |
16 | 4 | 2005/12/14 |
17 | 1 | 2007/05/05 |
18 | 2 | 2004/05/03 |
19 | 3 | 2008/06/15 |
20 | 4 | 2004/11/26 |
21 | 1 | 2003/03/07 |
22 | 2 | 2008/07/01 |
23 | 3 | 2002/03/21 |
24 | 4 | 2004/11/26 |
25 | 1 | 2008/05/19 |
26 | 2 | 2004/05/02 |
Output Option #1 (Ideal) | |||
Course_ID | Fee_ID | Start_Date | Fee Amount |
1 | 1 | 2002/02/20 | 0 |
2 | 2 | 2002/04/02 | 45 |
3 | 3 | 2003/04/03 | 12 |
4 | 4 | 2004/04/03 | 150 |
5 | 1 | 2006/04/20 | 1 |
6 | 2 | 2006/05/31 | 60 |
7 | 3 | 2007/06/01 | 19 |
8 | 4 | 2008/06/01 | 180 |
9 | 1 | 2004/03/21 | 0 |
10 | 2 | 2002/04/03 | 45 |
11 | 3 | 2003/04/04 | 12 |
12 | 4 | 2003/03/21 | 0 |
13 | 1 | 2008/05/19 | 1 |
14 | 2 | 2005/05/16 | 49 |
15 | 3 | 2005/05/02 | 14 |
16 | 4 | 2005/12/14 | 151 |
17 | 1 | 2007/05/05 | 1 |
18 | 2 | 2004/05/03 | 48 |
19 | 3 | 2008/06/15 | 22 |
20 | 4 | 2004/11/26 | 150 |
21 | 1 | 2003/03/07 | 0 |
22 | 2 | 2008/07/01 | 58.75 |
23 | 3 | 2002/03/21 | 0 |
24 | 4 | 2004/11/26 | 150 |
25 | 1 | 2008/05/19 | 1 |
26 | 2 | 2004/05/02 | 49 |
Output Option #2 (would allow for a lookup based on between) | |||
Fee_ID | Start_Date | End_Date | Fee Amount |
1 | 2002/01/01 | 2003/01/15 | 0 |
1 | 2003/01/16 | 2004/01/30 | 0 |
1 | 2004/01/31 | 2005/02/13 | 0 |
1 | 2005/02/14 | 2006/02/28 | 0 |
1 | 2006/03/01 | 2007/03/15 | 1 |
1 | 2007/03/16 | 2008/03/29 | 1 |
1 | 2008/03/30 | 1 | |
2 | 2002/01/02 | 2003/01/16 | 45 |
2 | 2003/01/17 | 2004/01/31 | 35 |
2 | 2004/02/01 | 2005/02/14 | 48 |
2 | 2005/02/15 | 2006/03/01 | 49 |
2 | 2006/03/02 | 2007/03/16 | 60 |
2 | 2007/03/17 | 2008/03/30 | 55 |
2 | 2008/03/31 | 2009/01/02 | 58.75 |
2 | 2009/01/03 | 62 | |
3 | 2003/01/18 | 2004/02/01 | 12 |
3 | 2004/02/02 | 2005/02/15 | 13 |
3 | 2005/02/16 | 2006/03/02 | 14 |
3 | 2006/03/03 | 2007/03/17 | 15 |
3 | 2007/03/18 | 2008/03/31 | 19 |
3 | 2008/04/01 | 2009/01/03 | 22 |
3 | 2009/01/04 | 2010/01/18 | 22.75 |
3 | 2010/01/19 | 38 | |
4 | 2004/02/03 | 2005/02/16 | 150 |
4 | 2005/02/17 | 2006/03/03 | 151 |
4 | 2006/03/04 | 2007/03/18 | 156 |
4 | 2007/03/19 | 2008/04/01 | 175 |
4 | 2008/04/02 | 2009/01/04 | 180 |
4 | 2009/01/05 | 2010/01/19 | 200 |
4 | 2010/01/20 | 195 |
hi, @cdn4lf
For your case, I recommend you add a start and end date for Effective Date in table1
Then use this formula to add a column.
Column = CALCULATE(SUM(Table1[Fee Amount]),FILTER(RELATEDTABLE(Table1),Table1[Effective_Start_Date]<=Table2[Start_Date]&&Table2[Start_Date]<=Table1[Effective_End_Date]))+0
Buy the way, for the last Effective Date, add 12/31/9999 for end date
Result:
Note: For courseID 26, the correct result should be 48
and here is pbix file, please try it.
Best Regards,
Lin
Hi Lin,
I don't have the ability to add an end data column to the source data, and the source Course_Fee Table is over 1000+ lines. If there a way to calculate the end date?
Sean
hi, @cdn4lf
You could use this formula to add a Effective_End_Date column in table 1, then create another column in table2
Effective_End_Date = IF ( CALCULATE ( MIN ( Table1[Effective_Start_Date] ), FILTER ( Table1, Table1[Fee_ID] = EARLIER ( Table1[Fee_ID] ) && Table1[Effective_Start_Date] > EARLIER ( Table1[Effective_Start_Date] ) ) ) = BLANK (), DATE ( 9999, 12, 31 ), CALCULATE ( MIN ( Table1[Effective_Start_Date] ), FILTER ( Table1, Table1[Fee_ID] = EARLIER ( Table1[Fee_ID] ) && Table1[Effective_Start_Date] > EARLIER ( Table1[Effective_Start_Date] ) ) ) - 1 )
Best regards,
Lin
That worked perfectly! Thank you so much!
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 | |
98 | |
77 | |
66 | |
54 |
User | Count |
---|---|
144 | |
104 | |
100 | |
86 | |
64 |