Forum Discussion
Calculate End Date
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!
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
6 Replies
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
