Frequent Visitor

7 years ago

Solved

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 t...

Need Help

v-lili6-msft

7 years ago

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

cdn4lf

Frequent Visitor

7 years ago

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

v-lili6-msft

Community Support

7 years ago

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

Calculate End Date.pbix27.1 MB

cdn4lf

Frequent Visitor

7 years ago

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

v-lili6-msft

Community Support

7 years ago

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

cdn4lf
Frequent Visitor
7 years ago
That worked perfectly! Thank you so much!

Forum Discussion

Calculate End Date

Related content

Calculate different end dates.

Between date filter with fixed start date and latest end date

Calculate Week Ending Date

Date Slicer not Responsive to Calculated Date Table

End date count