Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

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

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 on date rangefilter by single valuefilter 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
If this post helps, please consider accept as solution to help other members find it more quickly.

View solution in original post

8 REPLIES 8
Abduvali
Skilled Sharer
Skilled Sharer

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

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 😉

 

 

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 on date rangefilter by single valuefilter 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
If this post helps, please consider accept as solution to help other members find it more quickly.

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

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
If this post helps, please consider accept as solution to help other members find it more quickly.

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

Hi @jaco1951,

 

Please share the sample pbix file to test. 

 

Regards,
Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.