Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi ,
I am relatively new to PBI, i am trying to compute for the Overdue Dates filtered by months.
My table are formatted as follows , i have a separate date table:
Date Loan # Attribute Amount
8/1/2019 A123 Release 5,000
8/25/2019 A123 Collection 1,000
9/20/2019 A123 Collection 600
12/20/2019 A123 Collection 500
Overdue date is simply 60 days after the Release date. My end goal is to compute for the Overdue Date and the Overdue Amount ( Release amount Less Collection ) with Months as my slicer.
I have tried multiple measures and video tutorials but still having hard time figuring it out.
I hope you can help me, a proforma measure that i can study will be much appreciated.
Thank you very much!
Hi @jmcph ,
1.My sample data is this.
Date | Loan # | Attribute | Amount |
8/1/2019 | A123 | Release | 5000 |
8/25/2019 | A123 | Collection | 1000 |
9/20/2019 | A123 | Collection | 600 |
12/20/2019 | A123 | Collection | 500 |
7/5/2019 | A124 | Release | 6000 |
8/5/2019 | A124 | Collection | 1500 |
9/15/2019 | A124 | Collection | 650 |
10/15/2019 | A124 | Collection | 500 |
9/12/2019 | A125 | Release | 4000 |
9/20/2019 | A125 | Collection | 600 |
10/10/2019 | A125 | Collection | 500 |
12/15/2019 | A125 | Collection | 100 |
2.Create a separate date table and let Month column sorts by sort column. There is no relationship between two tables.
Calendar =
ADDCOLUMNS (
CALENDAR ( DATE ( 2019, 8, 1 ), DATE ( 2019, 12, 31 ) ),
"Month", FORMAT ( [Date], "MMM" ),
"Sort", MONTH ( [Date] )
)
3.Create a calculated column to calculate the Due date.
Due =
IF ( [Attribute] = "Release", [Date] + 60 )
4.Create a measure to calculate the Overdue Amount.
Overdue Amount =
VAR ramount =
IF (
ISFILTERED ( 'Calendar'[Month] )
&& SELECTEDVALUE ( 'Calendar'[Month] ) = FORMAT ( MAX ( 'Table'[Due] ), "MMM" ),
SUM ( 'Table'[Amount] )
)
VAR caomount =
IF (
ramount <> BLANK (),
CALCULATE (
SUM ( 'Table'[Amount] ),
FILTER (
ALLEXCEPT ( 'Table', 'Table'[Loan #] ),
[Attribute] = "Collection"
&& [Date] <= MAX ( 'Table'[Due] )
)
)
)
RETURN
ramount - caomount
4.The result is this.
You can check more details from here.
Best Regards,
Stephen Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
114 | |
101 | |
78 | |
75 | |
49 |
User | Count |
---|---|
145 | |
108 | |
107 | |
89 | |
61 |