Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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 .
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.
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.
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
Hi,
You may download my PBI file from here.
Hope this helps.
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
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:
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.
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:
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:
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.
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"
Proud to be a Super User!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
109 | |
99 | |
77 | |
66 | |
54 |
User | Count |
---|---|
144 | |
104 | |
102 | |
87 | |
64 |