DAX formula to calculate Allocated Revenue based on last year total revenue

[fjmontes]

I want to create a DAX formula as follows 

I have 4 columns Deal#, Unit, Year, Proposed Budget.  I want to allocate revenue based on the % increase between last year total proposed budget and this year propose budget.

Say 

2017 Proposed Budget was $115,000 and

2018 Proposed Budget was $125,000

% increase is 8.70%

(125000-115000)/115000

Now, for 2018 I have to allocate revenue as follows

(1 + 0.0870) * 2018 Proposed Budget 

1.0870 * Each unit proposed budget

I tried this but it did not work.  I have many deals 

Created four measures

1)

  TotalProposedBudget = SUM(Contract[ProposedBudget])

2) 

TotalProposedBudgetLY= CALCULATE(Contract[TotalProposedBudget],SAMEPERIODLASTYEAR(Contract[contractenddate].[Date]))

3)

Delta TY & LY = [TotalProposedBudget ] - [TotalProposedBudgetLY]

4)

% Delta = [Delta TY & LY] / [TotalProposedBudget ]

The calculation is done at the row level.  It is not an aggregate of the Total for the year.  Please help

[Anonymous]

It looks like you need a calculated column in your table, Allocated Revenue. So, a question is: Why don't you use Power Query?

Unless I miss something...

Best
Darek

[fjmontes]

I tried using a calculated column but it sum the entire table I need it to sum only by deal#, and last year.  I am using Power BI for a visualization.  

[fjmontes]

I am new at DAX and I am having difficulties unterstanding the use of filters at the row level.  How can I define a filter at the row level.

[Anonymous]

You should forget DAX and jump into Power Query. This is the best advice I can give you. DAX is not a tool to do this kind of stuff unless... you want to have nightmares.

Best
Darek

[fjmontes]

Thanks for your advise.  But how would I do that in power query.  I will try it.

[Anonymous]

I'll do that for you but you have to attach a file where you have some example data.

Best
Darek

[fjmontes]

https://app.powerbi.com/groups/51b8fe5d-7153-43e5-b0bd-82d23f638902/reports/29a5ec84-5356-4def-8064-...

I am trying to upload the report but I cannot do it.  here is the link to the report how can I share it with you.

[Anonymous]

By the way... I cannot access the report via the link.

Best
D.

[fjmontes]

https://drive.google.com/file/d/1LoLGTkkvuoigte0iaq-1Y1RrnTXXESIn/view?usp=sharing

Here is the file.

Thanks for all your help.

[Anonymous]

Will get down to it when I'm back home in about an hour...

Best
D.

[fjmontes]

Thank you!

[Anonymous]

You can paste a link to the file if you put it in OneDrive or Google Drive, or Dropbox. Don't forget to set the permissions correctly, so that I can access it.

Best
D.

[Anonymous]

I said POWER QUERY. Not DAX.

Best
Darek