Allocating revenue across multiple months/days

[Ben_G]

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.

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.

[Ashish_Mathur]

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.

[Ben_G]

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.

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

[Ashish_Mathur]

Hi,

You may download my PBI file from here.

Hope this helps.

[Ben_G]

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]

@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]

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]

@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"