cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
tmears
Helper III
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
v-jiascu-msft
Microsoft
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.
tmears
Helper III
Helper III

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

v-jiascu-msft
Microsoft
Microsoft

Hi @tmears,

 

 

Please download the demo of DAX from the attachment.

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 (),
    "Monthly", [Received Date],
    "Quarterly", EOMONTH ( [Received Date], 2 ),
    "6 Months", EOMONTH ( [Received Date], 5 ),
    "Annual", EOMONTH ( [Received Date], 11 )
)

3. Create a result table.

Result =
DISTINCT (
    SELECTCOLUMNS (
        ADDCOLUMNS (
            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]
    )
)

spread-revenue-over-future-months

 

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.

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

Hi,

 

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


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

yes of course

Many thanks

Hi,

 

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


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

Hi @tmears,

 

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

Result 2 =
DISTINCT (
    SELECTCOLUMNS (
        ADDCOLUMNS (
            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]
    )
)

spread-revenue-over-future-months2

 

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.
Stachu
Community Champion
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"}),
    #"Added Custom" = Table.AddColumn(#"Expanded Allocation", "NrOfMonths", each {0..([Spread]-1)}),
    #"Expanded Custom" = Table.ExpandListColumn(#"Added Custom", "NrOfMonths"),
    #"Added Custom1" = Table.AddColumn(#"Expanded Custom", "Allocated Date", each Date.AddMonths([Sale Date],[NrOfMonths])),
    #"Added Custom2" = Table.AddColumn(#"Added Custom1", "Allocated Revenue", each [Revenue]/[Spread]),
    #"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!

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

Stachu
Community Champion
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!

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group

April Update

Check it Out!

Click here to read more about the April 2021 Updates!

MBAS Carousel

Sign up for our May 4th event!

May the fourth be with you, join us online!

secondImage

The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.