Sum of Cash Payments by project in one table casted to standard Accrual percentages and dates

[mgrayTCB]

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

[mgrayTCB]

Thank you so much for your help. This helped me understand what I was doing wrong.

[mgrayTCB]

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.

[v-rzhou-msft]

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.

[mgrayTCB]

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....

AmtAccured = [Total Fee by Project] * sum(dimMilestones[AccuralRate])

Total Fee by Project =

CALCULATE(

sum(CashDates[NetFee]),

FILTER(all(CashDates),

CashDates[Project] = max(milestoneDates[Project])

)

)

Thanks again for your help

[v-rzhou-msft · Accepted 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.

[mgrayTCB]

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.