Hello!!
I need guidance from all the experts here. I’m stuck in my model and I want to derive some calculations in it and I am struggling with it.
I have created the measures and I’m facing issues in my “Bou” and “C Bou” numbers.
So “Bou#” is calculated from Presentation Data by counting the Loan ID where status = Non-Tech and "C Bou# is also same just that it is linked with Disb Date so to calculate the current month bounce only.
My Master file has the m.o.m data for the customer’s repayment performance (it might have live and closed cases as well) and presentation data has m.o.m repayment status for the customers (Only Live Customers).
Please let me know if anything else is needed from me.
Thanks in advance for the efforts you guys would be putting in.
I have attached the sample file for the reference.
MODEL.pbix (3.4 MB)
Hi @kkrj.ankit,
Thanks for providing a PBIX file 
As for your model, don’t know why you’d need a table like this with a bidirectional relationship. My advise, remove it.
Measure for the “Bou” numbers:
Bou# =
VAR vMaster = DISTINCT( SELECTCOLUMNS( FILTER( 'MASTER', 'MASTER'[Status] = "LIVE" ), "LoanID", MASTER[Loan ID] ))
VAR vPresent = DISTINCT( SELECTCOLUMNS( FILTER( 'Presentation Data', 'Presentation Data'[Bounce Status] = "NON TECH" ), "LoanID", 'Presentation Data'[Loan ID] ))
VAR Result = COUNTROWS( INTERSECT( vMaster, vPresent ))
RETURN
COALESCE( Result, 0 )
and measure for the “C Bou” numbers
C Bou# =
VAR vMaster = DISTINCT( SELECTCOLUMNS( CALCULATETABLE( MASTER, USERELATIONSHIP('Calendar'[Date], 'MASTER'[Disb_Date]), 'MASTER'[Status] = "LIVE" ), "LoanID", MASTER[Loan ID] ))
VAR vPresent = DISTINCT( SELECTCOLUMNS( FILTER( 'Presentation Data', 'Presentation Data'[Bounce Status] = "NON TECH" ), "LoanID", 'Presentation Data'[Loan ID] ))
VAR Result = COUNTROWS( INTERSECT( vMaster, vPresent ))
RETURN
COALESCE( Result, 0 )
.
In your report the date slicer contained a date field from the Master fact table, I replaced that with the Date field from your Date dimension table. To avoid wrong selections like these, make a habit of hiding fact tables key fields from report view.
I hope this is helpful. Here’s your file.
MODEL.pbix (3.3 MB)
1 Like
Hi @Melissa
Awesome work you’re genius. Thanks for your effort and time here.
It looks good I’ll review it and will get in touch with you if any issues.
Thanks for recommending the date table, I was just checking my numbers with another column will definitely change it to the calendar date.
Thanks
Ankit
Hi @Melissa
I checked the data, I think I missed highlighting this point that data we are getting from Presentation Data and consists of “presentation date” when we talk about current bounce then only customers in Apr’20 should be calculated with reference to the master date (Apr’20).
Secondly, the date that I was using was the date in master data for the month the customer status is uploaded or changed and I would like to go by that date so to have the bounce (NON TECH) of those customers present in that particular month in the presentation data.
So should I remove the date table? Would it be right?
So Ideally I want to see Bounce of the (Active Cust# i.e. live customers) and for the current month only.
Any help would be appreciated.
MODEL.pbix (3.3 MB)
Most certainly not. The date table is required for every time intelligence calculation. If you omit it from the model PBI will create separate date tables for every date field in your model to fill that gab…
.
I’m having trouble understanding the requirement and don’t want to guess…
When I filter on the MASTER[Date] column (Apr’20) I get records but if I filter on Presentation data[Presentation date] there aren’t any records for Apr’20…
.
How do you want to identify the current month? Does that depend on the Slicer selection?
Thanks!
Yes maybe because, as there is a limit for uploading the file size I would have filtered that data.
Yes as suggested by you if the user uses the date slicer from calendar table and if the user makes the selection from earliest to the max available date then it should consider the bounce for the current month (latest data month) divided by the active cases.
Similarly, if my max date is reduced from Apr’20 to Mar then the numerator should be (previous month bounce) i.e. Mar’20 and denominator should be active cases only.
So ideally I’ll make this date as a drop-down so there will be single month selected if the above scenario is not possible.
Thanks!
Okay based on that and with the switch to a Month & Year slicer I see no issue with the above provided solution. But I hadn’t looked at the % to fix those I updated the measures for Total Bou & Total C Bou.
Date filtering now flows from the Calendar[Date] down to both MASTER[Date] and Presentation data[Presentation date]. Although I’m not getting any C Bou results at the moment when a single month is selected but as you’ve indicated that could be due to the limited data…
Let me know if this solves it for you. Otherwise please provide a clear calculation example with expected outcome that I can check with your model. Thanks.
MODEL v2.pbix (3.2 MB)
