Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
I have some data which contains the following (example):
Issue Date, Amount, Maturity Date
01.01.2024, 10,000, 20.10.2024
05.01.2024, 12,000, 15.10.2024
08.01.2024, 8,000, 24.10.2024
etc.
so on the 06.02.2024, it would show that there was 22,00 outstanding. on the10.01.2024, it would show that there was 30,000 outsanding. On the 16.01.2024, this will now only show 18,000. and so forth.
What I want to do is create a table or graph which will show me, on any given date, how much is outstanding.
For any given date, Outstanding is how much, and the amount, were issued on or before that date (preferably midnight to midnight) and the maturity is on or after that date(preferably midnight to midnight).
Solved! Go to Solution.
Thanks for the reply from @amitchandak , please allow me to provide another insight:
Hi @djohanlon ,
You need to create a table of dates without a relationship and then try the following formula:
Calendar =
SELECTCOLUMNS (
CALENDAR(DATE(2024, 1, 1), DATE(2024, 10, 20)),
"DateKey", [Date],
"Year", YEAR ( [Date] ),
"Month Number", MONTH ( [Date] ),
"Month Name", FORMAT ( [Date], "MMMM" ),
"Day", DAY ( [Date] ),
"Day of Week", WEEKDAY ( [Date] )
)
Outstanding Debt =
CALCULATE (
SUM ( 'Debt'[Amount] ),
FILTER (
'Debt',
'Debt'[Issue Date] <= MAX ( 'Calendar'[Date] )
&& 'Debt'[Maturity Date] >= MAX ( 'Calendar'[Date] )
)
)
Best Regards,
Adamk Kong
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Thanks for the reply from @amitchandak , please allow me to provide another insight:
Hi @djohanlon ,
You need to create a table of dates without a relationship and then try the following formula:
Calendar =
SELECTCOLUMNS (
CALENDAR(DATE(2024, 1, 1), DATE(2024, 10, 20)),
"DateKey", [Date],
"Year", YEAR ( [Date] ),
"Month Number", MONTH ( [Date] ),
"Month Name", FORMAT ( [Date], "MMMM" ),
"Day", DAY ( [Date] ),
"Day of Week", WEEKDAY ( [Date] )
)
Outstanding Debt =
CALCULATE (
SUM ( 'Debt'[Amount] ),
FILTER (
'Debt',
'Debt'[Issue Date] <= MAX ( 'Calendar'[Date] )
&& 'Debt'[Maturity Date] >= MAX ( 'Calendar'[Date] )
)
)
Best Regards,
Adamk Kong
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
@djohanlon , I think same approach as Active/current employee should help
Power BI: HR Analytics - Employees as on Date : https://youtu.be/e6Y-l_JtCq4
https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-tr...
Power BI HR Active Employee Tenure Bucketing, and Hired, Terminated, and Active employees: https://youtu.be/fvgcx8QLqZU
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
Ask questions in Eventhouse and KQL, Eventstream, and Reflex.
User | Count |
---|---|
85 | |
83 | |
66 | |
60 | |
58 |
User | Count |
---|---|
188 | |
111 | |
105 | |
78 | |
71 |