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

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
Community Support

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 Skilled Sharer
Skilled Sharer

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 Helper III
Helper III

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
Community Support

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

Highlighted
jaco1951 Helper III
Helper III

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
Community Support

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 Helper III
Helper III

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
Community Support

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 Helper III
Helper III

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
New Ranks Launched March 24th!

New Ranks Launched March 24th!

The time has come: We are finally able to share more details on the brand-new ranks coming to the Power BI Community!

‘Better Together’ Contest Finalists Announced!

‘Better Together’ Contest Finalists Announced!

Congrats to the finalists of our ‘Better Together’-themed T-shirt design contest! Click for the top entries.

Arun 'Triple A' Event Video, Q&A, and Slides

Arun 'Triple A' Event Video, Q&A, and Slides

Missed the Arun 'Triple A' event or want to revisit it? We've got you covered! Check out the video, Q&A, and slides now.

Join THE global Microsoft Power Platform event series.

Join THE global Power Platform event series.

Attend for two days of expert-led learning and innovation on topics like AI and Analytics, powered by Dynamic Communities.

Community Summit North America

Community Summit North America

Innovate, Collaborate, Grow. The top training and networking event across the globe for Microsoft Business Applications

Top Solution Authors