Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started
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
Solved! Go to 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:
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
Hi @jaco1951,
Can you provide some sample data?
Regards
Abduvali
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 😉
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:
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
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
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
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
Check out the September 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
105 | |
99 | |
98 | |
38 | |
37 |
User | Count |
---|---|
154 | |
120 | |
73 | |
73 | |
63 |