cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
jaco1951 Member
Member

Pick all loans that are active in selected period

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

1 ACCEPTED SOLUTION

Accepted Solutions
Community Support Team
Community Support Team

Re: Pick all loans that are active in selected period

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:

 

7.PNGfilter on date range8.PNGfilter 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

Community Support Team _ Xiaoxin Sheng
If this post helps, please consider Accept it as the solution to help the other members find it more quickly
Learning resources: Power BI

View solution in original post

8 REPLIES 8
Abduvali Established Member
Established Member

Re: Pick all loans that are active in selected period

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 Member
Member

Re: Pick all loans that are active in selected period

Hi Abduvali

 

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

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 😉

 

 

Community Support Team
Community Support Team

Re: Pick all loans that are active in selected period

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:

 

7.PNGfilter on date range8.PNGfilter 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

Community Support Team _ Xiaoxin Sheng
If this post helps, please consider Accept it as the solution to help the other members find it more quickly
Learning resources: Power BI

View solution in original post

jaco1951 Member
Member

Re: Pick all loans that are active in selected period

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

Community Support Team
Community Support Team

Re: Pick all loans that are active in selected period

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

Community Support Team _ Xiaoxin Sheng
If this post helps, please consider Accept it as the solution to help the other members find it more quickly
Learning resources: Power BI
jaco1951 Member
Member

Re: Pick all loans that are active in selected period

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

Community Support Team
Community Support Team

Re: Pick all loans that are active in selected period

Hi @jaco1951,

 

Please share the sample pbix file to test. 

 

Regards,
Xiaoxin Sheng

Community Support Team _ Xiaoxin Sheng
If this post helps, please consider Accept it as the solution to help the other members find it more quickly
Learning resources: Power BI
jaco1951 Member
Member

Re: Pick all loans that are active in selected period

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

Helpful resources

Announcements
Exclusive LIVE Community Event #3 – Ask Arun Anything

Exclusive LIVE Community Event #3 – Ask Arun Anything

Join us in the third Triple A event!

Meet the 2020 Season 1 Power BI Super Users!

Meet the 2020 Season 1 Power BI Super Users!

It’s the start of a new Super User season! Learn all about the new Super Users and brand-new tiered recognition system.

Super User Challenge: Can You Solve These?

Super User Challenge: Can You Solve These?

We're celebrating the start of the New Super User season with our first ever Super User 'Can You Solve These?' challenge.

Power BI Desktop Update - February 2020

Power BI Desktop Update - February 2020

We are super excited for our update this month, as we are releasing two of our top community requests!

Power Platform Online Conference

Power Platform Online Conference

Join us for the first ever Power Platform Online Conference!

Top Solution Authors