Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get 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

Reply
mgrayTCB
Helper III
Helper III

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

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. 

mgrayTCB_0-1635424208550.png

 

Any help or ideas would be greatly appreciated.

 

The model looks like this

mgrayTCB_1-1635424684384.png

 

 

A simplified PBix file is here.

https://drive.google.com/file/d/1F-60fTrgFjDy53ycBb7uqlwDEjcNleP1/view?usp=sharing


Text tables pasted here:

CashDates

ProjectDateNetFee
AO Flats1/5/2022                300,000
AO Flats1/15/2023                300,000
AO Flats6/29/2023                150,000
AO Flats8/4/2023                250,000
ParkView On Blue3/31/2022               40,000.0
ParkView On Blue3/26/2023               80,000.0
ParkView On Blue6/24/2023             120,000.0
ParkView On Blue12/14/2023               20,000.0
M Mill5/30/2022                150,000
M Mill5/25/2023                200,000
M Mill7/24/2023                500,000
M Mill12/14/2023                  11,000
M Mill2/12/2024                  50,000

 


MilestoneDates

ProjectMilestoneMilestoneDate
AO FlatsPreClosing1/31/2019
AO FlatsClosing1/5/2022
AO FlatsCompletion2/27/2023
AO FlatsLeaseUp11/24/2023
ParkView On BluePreClosing3/27/2018
ParkView On BlueClosing4/5/2022
ParkView On BlueCompletion9/22/2023
ParkView On BlueLeaseUp11/14/2023
M MillPreClosing1/20/2024
M MillClosing5/30/2022
M MillCompletion9/22/2023
M MillLeaseUp1/28/2024


dimMilestones

MilestonesAccuralRate
Closing0.2
Completion0.55
LeaseUp0.1
PreClosing0.15


dimProjects

ProjectState
AO FlatsMA
ParkView On BlueNC
M MillDC
1 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.

1.png

Please check your relationships in model view.

2.png

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.

View solution in original post

6 REPLIES 6
mgrayTCB
Helper III
Helper III

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

mgrayTCB
Helper III
Helper III

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.

1.png

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

mgrayTCB_0-1635770609243.png

 

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

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.

1.png

Please check your relationships in model view.

2.png

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.

 

mgrayTCB_0-1636028855580.png

 

Helpful resources

Announcements
November Carousel

Fabric Community Update - November 2024

Find out what's new and trending in the Fabric Community.

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.

Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

Check out the November 2024 Power BI update to learn about new features.