Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreGet certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now
We project fees in a table called (for this simplified example) CashDates. It shows the expected dates of payment for each project. We also project project milestones in a table called MilestoneDates.
I am trying to take the total cash by project and allocate it by fixed accrual percentages (dimMilestones table) so to have a projected fee accrual stream by date.
In the end I want to be able to quickly switch a report between the cash projection by project to the accrual projection by project.
I must be missing something basic because In excel this would just require a few lookups to create a table that looks like this and provides me with milestone dates and accured fee on those dates.
Any help or ideas would be greatly appreciated.
The model looks like this
A simplified PBix file is here.
https://drive.google.com/file/d/1F-60fTrgFjDy53ycBb7uqlwDEjcNleP1/view?usp=sharing
Text tables pasted here:
CashDates
Project | Date | NetFee |
AO Flats | 1/5/2022 | 300,000 |
AO Flats | 1/15/2023 | 300,000 |
AO Flats | 6/29/2023 | 150,000 |
AO Flats | 8/4/2023 | 250,000 |
ParkView On Blue | 3/31/2022 | 40,000.0 |
ParkView On Blue | 3/26/2023 | 80,000.0 |
ParkView On Blue | 6/24/2023 | 120,000.0 |
ParkView On Blue | 12/14/2023 | 20,000.0 |
M Mill | 5/30/2022 | 150,000 |
M Mill | 5/25/2023 | 200,000 |
M Mill | 7/24/2023 | 500,000 |
M Mill | 12/14/2023 | 11,000 |
M Mill | 2/12/2024 | 50,000 |
MilestoneDates
Project | Milestone | MilestoneDate |
AO Flats | PreClosing | 1/31/2019 |
AO Flats | Closing | 1/5/2022 |
AO Flats | Completion | 2/27/2023 |
AO Flats | LeaseUp | 11/24/2023 |
ParkView On Blue | PreClosing | 3/27/2018 |
ParkView On Blue | Closing | 4/5/2022 |
ParkView On Blue | Completion | 9/22/2023 |
ParkView On Blue | LeaseUp | 11/14/2023 |
M Mill | PreClosing | 1/20/2024 |
M Mill | Closing | 5/30/2022 |
M Mill | Completion | 9/22/2023 |
M Mill | LeaseUp | 1/28/2024 |
dimMilestones
Milestones | AccuralRate |
Closing | 0.2 |
Completion | 0.55 |
LeaseUp | 0.1 |
PreClosing | 0.15 |
dimProjects
Project | State |
AO Flats | MA |
ParkView On Blue | NC |
M Mill | DC |
Solved! Go to Solution.
Hi @mgrayTCB
I try to reproduce your issue in my sample, however, this measure works fine in my sample. Here you can see I create a "0Measure" table and move measures into this table, it still works well.
Please check your relationships in model view.
Or you can download my sample and check it.
Best Regards,
Rico Zhou
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Thank you so much for your help. This helped me understand what I was doing wrong.
is there anything I can add to clairfy my question? I now have a complicating requirment but would first like to get an understanding of how to solve the simple version.
Hi @mgrayTCB
I create a sample by your sample data and my sample has the same relationship like yours.
Try measures as below.
TotaklFeeByF = CALCULATE(SUM(CashDates[NetFee]),FILTER(ALL(CashDates),CashDates[Project] = MAX(MilestoneDates[Project])))
AmtAccured = [TotaklFeeByF] * SUM(dimMilestones[AccuralRate])
Create a table visual.
Best Regards,
Rico Zhou
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Thank you for taking the time to look at this. When I create your measures and trying show the full table it gets filtered by the two dates that are in both the CashDates table and the MilestonesDate table and I dont know why....
Hi @mgrayTCB
I try to reproduce your issue in my sample, however, this measure works fine in my sample. Here you can see I create a "0Measure" table and move measures into this table, it still works well.
Please check your relationships in model view.
Or you can download my sample and check it.
Best Regards,
Rico Zhou
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Rico,
I thought this was working but I cant use it in the way I want. What am I missing?
I was hoping to use the two measures in the same table or graph to show two different streams of income. One for accuals and one for cash. When I put these two measures from your model in the same table I just get the same values.
Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
User | Count |
---|---|
87 | |
87 | |
84 | |
66 | |
49 |
User | Count |
---|---|
130 | |
110 | |
93 | |
70 | |
67 |