Limiting Date Ranges to Avoid Overlap

[kbl1726]

Hi All,

I'm working with financial data that comes out quarterly, consisting of foward looking figures (loan amounts and rates, for example), and therefore data points often overlap. I'd like to display the data that comes out each quarter as a time series, but only data points for the following 90 days from the end of a quarter, i.e., until new data comes out, in order to avoid overlap. This way, at the end of each quarter, I can see which loans are in effect over the next three months.  I can do that now but only for 1 period at a time. If I select multiple periods at a time, it stacks what are effectively duplicate data points. What I'd really like to do is see how those 90 day chunks change over time by limiting the date ranges displayed for each period. 

This data is reported at the end of each calendar quarter and is effective going forward.  So I'd like to limit any data disclosed at the end of Q1 2018 (March 31 2018) to the following 3 months, or Apr 1 to June 30 2018.  For Q2, I'd like to limit any data displayed to July 1 - Sept 30 2018, and so on. 

Period	Effective Start	Effective end
Q1 2018	4/1/2018	6/30/2018
Q2 2018	7/1/2018	9/30/2018
Q3 2018	10/1/2018	12/31/2018

My data is structured as follows.  Note that some start dates may be many months from the end of the quarter, such as the first row.  As the first row is not within Q1 2018's effective start/end, it would not be displayed.  Also, note that rows 3 and 5 are the same loan.  But I don't want the first instance of it to stretch from 4/1 to 9/30. Q1's effective range ends on 6/30, so that first instance would end at 6/30. The second instance, Q2, would run from 7/1 to 9/30. As I have it now, if multiple periods are selected, both instances of those loans are counted and stacked, so it looks like 2 loans across those dates. 

Period	Amount	Int. Rate	Start	End
Q1 2018	1653	3.1	7/1/2018	12/31/2018
Q1 2018	182	2.8	4/1/2018	6/30/2018
Q1 2018	3125	4.2	4/1/2018	9/30/2018
Q2 2018	1653	3.1	7/1/2018	12/31/2018
Q2 2018	3125	4.2	7/1/2018	9/30/2018
Q2 2018	1234	6.2	7/1/2018	12/31/2018
Q2 2018	548	4.1	10/1/2018	3/31/2019
Q3 2018	1653	3.1	10/1/2018	12/31/2018

Ideally, my X-Axis would be the effective dates, not the full date range in the 2nd table, and the Y-Axis the loan volumes and/or rates.  Any help is greatly appreciated.  Many thanks. 

[v-juanli-msft · Accepted solution]

Hi kbi,

As you described, I want to confirm with your requirements based on my understanding.

Do you want:

1. when the ‘start date’ is not within QX 2018's ‘Effective Start/End’, it would not be displayed
2. for dates displayed, each ‘end date’ should match the proper ‘Effective End’ by ‘period’.

If it is in this case, you could create DAX to meet your goal.

in your second table, create two calculated column to connect with your table1

effective start =RELATED ( Table1[Effective Start] )

effective end =RELATED ( Table1[Effective end] )

you will get the table like this

then create a new table

Table =
ADDCOLUMNS (
    FILTER (
        Table3,
        Table3[Start] >= Table3[effective start]
            && Table3[Start] <= Table3[effective end]
    ),
    "End2", IF ( Table3[End] <= Table3[effective end], Table3[End], Table3[effective end] )
)

finally,you will see

additionally,i don't understand  

 As I have it now, if multiple periods are selected, both instances of those loans are counted and stacked, so it looks like 2 loans across those dates. 

Ideally, my X-Axis would be the effective dates, not the full date range in the 2nd table, and the Y-Axis the loan volumes and/or rates.

---

could you kindly give me more details to understand better?

Regards,

Maggie