Calculate End Date

[cdn4lf]

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!

[v-lili6-msft]

hi, @cdn4lf 

You may need to use "all" function in your measure

https://www.sqlbi.com/articles/managing-all-functions-in-dax-all-allselected-allnoblankrow-allexcept...

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

[cdn4lf]

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]

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

[cdn4lf]

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 · Accepted 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

[cdn4lf]

That worked perfectly!  Thank you so much!