cancel
Showing results for
Did you mean:
Helper III

## spread revenue over future months

I am struggeling on this one.   have a data table which contains revnues recieved however some refer to monthly revenue and some relate to quarter.  My thought is to create a new table which based upon the period calculates revenue over the period

Example:

Sale Date = Jan 19

Period = Monthly

Revenue = 1200

Defer results:

Jan = 100

Sale Date = Jan 19

Period = Quarterly

Revenue = 1200

Defer results:

Jan = 400

Feb = 400

Mar = 400

i have a revenue column, recieved date, and period (which contains, Annual, 6 months, Monthly, One off and Quarterly)

seems simple but struggling, any help out there would be really apprpciated

My model....I have a date table...and a fact table.  Very basic

11 REPLIES 11
Microsoft

Hi @tmears,

Could you please mark the proper answers as solutions?

Best Regards,

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Helper III

thanks you guys, i will work through these and come back to you, really appriciate you help on this

Microsoft

Hi @tmears,

1. Add a column like below to the Date table if you don't have one.

`YearMonth = FORMAT([Date], "YYYY mmm")`

2. Add a column to the fact table to identify the period end.

```periodEnd =
SWITCH (
[Period],
"One off", BLANK (),
"Quarterly", EOMONTH ( [Received Date], 2 ),
"6 Months", EOMONTH ( [Received Date], 5 ),
"Annual", EOMONTH ( [Received Date], 11 )
)
```

3. Create a result table.

```Result =
DISTINCT (
SELECTCOLUMNS (
FILTER (
CROSSJOIN ( 'Table1', 'Calendar' ),
Table1[periodEnd] <> BLANK ()
&& [Date] <= Table1[periodEnd]
),
"Defer", SWITCH (
[Period],
"Monthly", [Revenue],
"Quarterly", [Revenue] / 3,
"6 Months", [Revenue] / 6,
"Annual", [Revenue] / 12
)
),
"Revenue", [Revenue],
"Period", [Period],
"DeferPeriod", [YearMonth],
"Defer", [Defer]
)
)
```

Best Regards,

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Helper III

thank you for your help, i have tried to apply this to my data and getting a few problems,

I have attached a sample file

bascially the results table is bring back previous figures, which i can understand why, and appears to not take into consideration the end period date

Super User III

Hi,

Would you be OK with a solution that combines DAX and M?

Regards,
Ashish Mathur
http://www.ashishmathur.com
Helper III

yes of course

Many thanks

Super User III

Hi,

See if the 3 column table in this PBI file helps you.

Regards,
Ashish Mathur
http://www.ashishmathur.com
Microsoft

Hi @tmears,

Please download the file from the attachment. More conditions are considered.

```Result 2 =
DISTINCT (
SELECTCOLUMNS (
FILTER (
CROSSJOIN (
SUMMARIZE (
'Table1',
[Account Number],
[Revenue type],
[Expected date],
[periodEnd],
"expectednet", SUM ( Table1[Expected Net] )
),
'Calendar'
),
Table1[periodEnd] <> BLANK ()
&& [Date] <= Table1[periodEnd]
&& [Date] >= [Expected date]
),
"Defer", SWITCH (
[Revenue type],
"Monthly", [expectednet],
"Quarterly", [ExpectedNet] / 3,
"6 Months", [ExpectedNet] / 6,
"Annual", [ExpectedNet] / 12
)
),
"Revenue", [ExpectedNet],
"Period", [Revenue type],
"DeferPeriod", [YearMonth],
"Defer", [Defer],
"Account Number", [Account Number]
)
)
```

Please don't share sensitive data here.

Best Regards,

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Community Champion

this is the easiest to do in M
I assume if sales are made in e.g. Feb you want to allocate them across next 3/12 months, not within current year

so sale in Feb 2019 with Monthly allocation will allocate from Feb 2019 to Jan 2020

Allocation table (adjust the name in the #"Merged Queries" in the output table if you use different name)

```let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8s3PK8nIqVTSUTI0UorViVYKLE0sSS0CixgrxcYCAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Period = _t, Spread = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Period", type text}, {"Spread", Int64.Type}})
in
#"Changed Type"```

the output table

```let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjIwtNQ1MNQ1tFTSUfLNzyvJyKkEsgyNDAyUYnVg8kbo8kYmKPJQ/YGliSWpRUgGxAIA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [#"Sale Date" = _t, Period = _t, Revenue = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Sale Date", type date}, {"Period", type text}, {"Revenue", Int64.Type}}),
#"Merged Queries" = Table.NestedJoin(#"Changed Type",{"Period"},Allocation,{"Period"},"Allocation",JoinKind.LeftOuter),
#"Expanded Allocation" = Table.ExpandTableColumn(#"Merged Queries", "Allocation", {"Spread"}, {"Spread"}),
#"Expanded Custom" = Table.ExpandListColumn(#"Added Custom", "NrOfMonths"),
#"Added Custom1" = Table.AddColumn(#"Expanded Custom", "Allocated Date", each Date.AddMonths([Sale Date],[NrOfMonths])),
#"Removed Columns" = Table.RemoveColumns(#"Added Custom2",{"Revenue", "Spread", "NrOfMonths"})
in
#"Removed Columns"```

Did I answer your question? Mark my post as a solution!
Thank you for the kudos 🙂

Proud to be a Super User!

Helper III

Many thanks, but slgihtly confused by this one... i was trying to work it in DAX?

Community Champion

you didn't really specify that in your original post, and as I said in the beginning of my first post - it's much easier to do in Query Editor with M rather than in DAX  🙂

nevertheless - have a read here
https://community.powerbi.com/t5/Desktop/Linear-Depreciation-DAX/m-p/492808#M229665
in that case I used GENERATE and GENERATESERIES to do the equivalent of expanding the list in M, hopefully it helps you to come up with the solution

Did I answer your question? Mark my post as a solution!
Thank you for the kudos 🙂

Proud to be a Super User!

Announcements

#### Happy New Year from Power BI

This is a must watch for a message from Power BI!