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 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
Hi @tmears,
Could you please mark the proper answers as solutions?
Best Regards,
thanks you guys, i will work through these and come back to you, really appriciate you help on this
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] ) )
Best Regards,
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?
yes of course
Many thanks
Hi,
See if the 3 column table in this PBI file helps you.
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] ) )
Please don't share sensitive data here.
Best Regards,
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"
Many thanks, but slgihtly confused by this one... i was trying to work it in DAX?
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
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 |
---|---|
114 | |
99 | |
83 | |
70 | |
60 |
User | Count |
---|---|
150 | |
115 | |
104 | |
89 | |
65 |