cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Ben_G
Frequent Visitor

Allocating revenue across multiple months/days

I have campaigns with can run from 1 day to multiple months.

Each campaign has revenue.

Each campaign has a start and and end date.

Here is some sample data

Campaign 

Start 

End

Amount 

971673

10/01/2020 

26/10/2020 

110000 

726270

05/05/2020 

09/05/2021 

140000

898094

04/08/2021 

26/08/2022

190000

972604

31/12/2020 

13/10/2021

30000

652157

29/02/2020 

20/10/2020

80000

955895

01/05/2020 

31/08/2021

150000

556421

25/05/2020 

24/05/2021

185000

 

I want to be able able to allocate revenue across the months including when there are partial months at the beginning and/or end of the campaign period.

 

I came across this post by @Fowmy .

https://community.powerbi.com/t5/Community-Blog/Split-Amount-Across-Months-Using-Power-Query/ba-p/18...

 

His Power Query solution appears to do exactly what I want. I downloaded his PBIX and tried to get the solution to work with my data. I followed the instructions and pasted the code into a blank query. Howver his data source is an Excel spreadsheet. My data is already in Power BI. I am a relative newbie and I tried everything I could think of to change the Source to point at my Power BI table but failed.

Ben_G_0-1656769518068.png

 

I have posted a comment for fowmy asking for help but have a client deadline to meet so am asking the wider community for help as well.

 

My request to the experts here is twofold:

One – can you help me with changing the data source to read from Power BI?

Two – is there an easier / better way to do this?

 

Any assistance would be much appreciated 🙌 maybe @ImkeF ??

 

My updated version of fowmy’s PBIX is linked here.

7 REPLIES 7
Ashish_Mathur
Super User
Super User

Hi,

In your visuals, what level of granularity would you want to go to - Day level or only month level?  For 961673, please clearly show how much amount should appear for each month.  Please also explain how you arrived at the amount for each month.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Hi @Ashish_Mathur 
Thanks for the response.

So first I modelled the data in Excel so I knew exactly the outcome I wanted.

In this linked spreadsheet (Month Year verify v2) I have highlighted the data for 961673 to show what I am looking to get.
Level of granularity - I'd like to be able to go to daily values (as in the spreadsheet). I have a date table in my full solution. 
Updated PBIX (Allocation2). I am looking to add updated M code to the 'Campaign_calcs' query.

Ben_G_0-1657176173249.png

Thanks for any help with either updated M code or a DAX solution (in a perfect world I'd have a measure that would do the calculation 🤞)

Ben_G

Hi,

You may download my PBI file from here.

Hope this helps.

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Hi @Ashish_Mathur 

Apologies for the slow response. Just back from a weeks holiday.

I will download your PBIX and take a look.

Thanks so much.

Ben G

Ben_G
Frequent Visitor

@DataInsights 

Thanks again for your input. I have now created a table from the Campaigns table.

let

Source = Table.SelectColumns(Campaigns,{"Campaign","Start","End","Amount"})

in

Source

Columns:

  • Campaigns is Text
  • Start and End are Dates
  • Amount is a whole number

Trying to use your M-code I am struggling to work out how to remove the transforms, etc. 

If there is any chnace you could rework you code to run directly after my source = table.selectcolumn... (ie beginning with step "Invoked Custom Function"?) I would be hugely grateful.

 

Thanks in advance.

Ben_G
Frequent Visitor

Hi @DataInsights 

Thanks for your help. I am still having problems.

Your updated solution specifies

‘Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("XY/LDQQhDENbQ…’

What I was trying to do was read from an existing table and select specific columns,

Example:

  • Table Name = Campaigns
  • Column name = Campaign - Text
  • Column Name = Start Date - Date
  • Column Name = End Date - Date
  • Column Name  Revenue - Number

So for the source, eg using table.addrows or table.addcolumns, I’d like to be able to specify the specific rows / columns and possibly add others. Can you assist with helping me specify the source?

Stepping back to a higher level view, take a look at this spreadsheet. In it I have tabs with:

  • Data
  • Calculation table
  • Flat table
  • Pivot table

In a perfect world in Power BI I’d like to be able to create a flat table (linked to my date table) which I could then use to create a matrix in my dashboards. Could you help me with that? I found Foumy’s solution which I thought would work but what I am asking for here would be a better solution from my perspective.

Thanks in advance for any help.

DataInsights
Super User
Super User

@Ben_G,

 

Try this in Power Query. I had to add the Days column to the Campaign table in order for the solution to work. Then, I copied the M code beginning with step "Invoked Custom Function" and pasted it in the Campaign table.

 

Great solution, @Fowmy!

 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("XY/LDQQhDENbQZxHGycQSGpB038bCyzzYSUORvaT49aiVy41xSMyMUggGLqrcn0CMw4AHyDE82ixSpE6HCj1t2JKPiRPIu+EucFzd4zyFbJVIRPwHfDegQGwUOLXWZzukrQjRYW1dkNI/D3kXhXsr0TVXOfpd4Wtut8M3YEZlddiyU/W9MmeXw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Campaign = _t, Start = _t, End = _t, Amount = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Campaign", Int64.Type}, {"Start", type date}, {"End", type date}, {"Amount", Int64.Type}}),
    #"Replaced Value" = Table.ReplaceValue(#"Changed Type",null,556421,Replacer.ReplaceValue,{"Campaign"}),
    #"Added Custom2" = Table.AddColumn(#"Replaced Value", "Days", each Duration.Days([End] - [Start]) + 1),
    #"Changed Type3" = Table.TransformColumnTypes(#"Added Custom2",{{"Days", Int64.Type}}),
    #"Invoked Custom Function" = Table.AddColumn(#"Changed Type3", "Months", each fnPeriod([Start], [End])),
    #"Expanded Months" = Table.ExpandTableColumn(#"Invoked Custom Function", "Months", {"fDate", "fDay"}, {"fDate", "fDay"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Expanded Months",{{"fDate", type date}, {"fDay", Int64.Type}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type1", "Monthly Amount", each [Amount]/[Days] * [fDay]),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "Month-Year", each Date.EndOfMonth([fDate])),
    #"Removed Other Columns" = Table.SelectColumns(#"Added Custom1",{"Campaign", "Monthly Amount", "Month-Year"}),
    #"Changed Type2" = Table.TransformColumnTypes(#"Removed Other Columns",{{"Monthly Amount", type number}, {"Month-Year", type date}})
in
    #"Changed Type2"

 





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Helpful resources

Announcements
August 2022 update 768x460.jpg

Check it Out!

Click here to learn more about the August 2022 updates!

August 1 episode 9_no_dates 768x460.jpg

The Power BI Community Show

Watch the playback when Priya Sathy and Charles Webb discuss Datamarts! Kelly also shares Power BI Community updates.

Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

Top Solution Authors
Top Kudoed Authors