Pick all loans that are active in selected period

[jaco1951]

Dear All

I have a table with payment structure on loans. I have another table for dates, calendar table. Currently these are linked on PaymentDate (Due date)

If I select year 2017, month 5 from my Calendar I would like to be able to filter not only what loan that have due-date in May, but I would also like to be able to see all loans that match this (the formula might not be correct, but it should give an idea on what I am trying to create):

FindAllActiveLoan_Filter:

If(Loam[FromDate] <= Max(Calendar[Date]) && Loan[PaymentDate] >= Max(Calendar[Date]), 1, 0)

I am not able to make this work with the two tables, my knowledge seems to stop...

I would use this 1, 0 flag to sum all loans, calculate weighted floating rate and fixed rate, look at total balance in different periods etc.

Br

Espen

[Abduvali]

Hi @jaco1951,

Can you provide some sample data?

• Why do you need Calendar table? Why not use PaymentDate in your date slicer this will already display all payments that due in a chosen month.

Regards

Abduvali

[jaco1951]

Hi Abduvali

Here is a list of the dates, fromdate and payment date. 

So if I select 2017 and month 03, I would like to catch the loans that have "PaymentDate" greather than the selected date, and at the same time "fromDate" needs to be less than my selection. 

Currenty I can only find what loan that are due for paying in month 03.

I use calendar because I was used to do it this way in QlikView, I am not sure that it would make any diffence for this problem, but I might be wrong.

Thank you 😉

[v-shex-msft · Accepted solution]

HI @jaco1951,

You can try to use below formula to filter suitable records by slicer:

Measures:

Tag(Range)=
VAR temp_Calendar =
    CALENDAR ( MAX ( 'Table'[Start Date] ), MAX ( 'Table'[End Date] ) )
RETURN
    IF (
        FIRSTDATE ( 'Calendar'[Date] ) IN temp_Calendar
            && LASTDATE ( 'Calendar'[Date] ) IN temp_Calendar,
        1,
        0
    )
	
Tag(Single)=
IF (
    MAX ( 'Calendar'[Date] )
        IN CALENDAR ( MAX ( 'Table'[Start Date] ), MAX ( 'Table'[End Date] ) ),
    1,
    0
)

Then drag above measure(one of them) to visual level filter, switch mode to advanced and setting to filter value 'is' 1.

Result:

filter on date rangefilter by single value

Notice:

1. the source column of date slicer can't from  table who stored startdate and enddate, these tables also can't contains relationship.

2. 'range' version of measure can works on single value and date range; 'single' measure only works on single value.

Regards,

Xiaxin Sheng

[jaco1951]

Dear Xiaxin Sheng

Thank very much. Excellent solution!

I have just one more question, I would like to select on yearmonth basis, like 201711, and get data only for the last day of the month. I have made a new measure creating a flag where Date = EndOfMonth(Date), but i was wondering if there are better ways to solve this.

Can you assist me on how to do that? 

Br Espen

[v-shex-msft]

Hi @jaco1951,

You can add 'year month' column to calendar, then write a measure compare current date with calendar date.

I haven't found a way to direct use 'year month' to filter not exist date range, so I use measure to add tag to filter records.

Tag = 
    IF (
        LASTDATE('Calendar'[Date]) =    MAX ( 'Table'[End Date] ),
        1,
        0
    )

BTW, 'end of month' also works for your scenario, but if you current table date to analysis, this function won't work.

Regards,

Xiaoxin Sheng

[jaco1951]

Dear Xiaoxin Sheng

One final thing, how can I use this calendar with Tag(Single) to create KPI or Cards with totals for the selected period? If I add it to the formula, it seems to have no impact on the result.

balance_USD_current_TAG =
CALCULATE(SUM(Loan[startBalance]) * SUM(Loan[Currency_latest]); FILTER(calendarActiveLoan;[Tag(Range)] = 1))

Kind regards

Espen

[v-shex-msft]

Hi @jaco1951,

Please share the sample pbix file to test. 

Regards,
Xiaoxin Sheng

[jaco1951]

Dear Xiaoxin

I will try to create a test data pbix file, I cannot share the data I have because of regulations.

I really appreciate your help.

Best regards

Espen