'Daily' Deferred Revenue Model

[Anonymous]

Good Morning all,  I am struggling with this one, I have tried the various 'Deferred Models' Solution to no avail, so I wondered if anyone can try their hand with this one.

I have a Calendar Table, has all the usual and required fields. 

My Fact table, has 

Start Date, End Date,CustCode,Product Group, Total Amount Paid.

Fact table:

5/4/2021,4/7/2021,1000,Activation,25.00

6/4/2021,5/7/2021,2000,Activation,25.00

I need to show the daily amount  for each day in my report.

Not sure if it's a Measure or to make a temp table.

 but I am assuming I need the result to look like this for me to report on.

5/04/2021	1000	Activation	0.277778
6/04/2021	1000	Activation	0.277778
7/04/2021	1000	Activation	0.277778
8/04/2021	1000	Activation	0.277778
9/04/2021	1000	Activation	0.277778
10/04/2021	1000	Activation	0.277778
11/04/2021	1000	Activation	0.277778
12/04/2021	1000	Activation	0.277778
13/04/2021	1000	Activation	0.277778
14/04/2021	1000	Activation	0.277778
15/04/2021	1000	Activation	0.277778
16/04/2021	1000	Activation	0.277778
17/04/2021	1000	Activation	0.277778
18/04/2021	1000	Activation	0.277778
19/04/2021	1000	Activation	0.277778
20/04/2021	1000	Activation	0.277778
21/04/2021	1000	Activation	0.277778
22/04/2021	1000	Activation	0.277778
23/04/2021	1000	Activation	0.277778
24/04/2021	1000	Activation	0.277778
25/04/2021	1000	Activation	0.277778
26/04/2021	1000	Activation	0.277778

etc etc until 4/7/2021

How... do I do that?  now I have 2 Mil rows so potentially 2 mil different Customers.

But if I can get the basis working with a small amount of data I can work out the rest... just need to get this started.

Any help much appreciated.

[Anonymous]

Hi , I managed to glean what you've done there, I have been able to replicate, thankfully we can group the data to a more managable, ( for this solution) amount and was then able to replicate.

Many thanks. I have however decided to go the DAX measure route. now after the user has given proper examples of the need. Having created and answer in Excel with a 4 layered IF AND statement, I shoudl be able to get this set as a Measure.  May thanks. Mike.

[Anonymous]

Hi, I am interested in the solution you ended up with. Can you please share? 

[serpiva64 · Accepted solution]

Hi,

i obtained this:

with these steps:

let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMtU30TcyMDLUMdE3hzAMDQwMdByTSzLLEksy8/N0jEx1DAyUYnWilcxgak1hao2wq40FAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}}),
#"Split Column by Delimiter" = Table.SplitColumn(#"Changed Type", "Column1", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), {"Column1.1", "Column1.2", "Column1.3", "Column1.4", "Column1.5"}),
#"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Column1.1", type date}, {"Column1.2", type date}, {"Column1.3", Int64.Type}, {"Column1.4", type text}, {"Column1.5", Int64.Type}}),
#"Added Custom" = Table.AddColumn(#"Changed Type1", "Custom", each ([Column1.2]-[Column1.1])),
#"Changed Type2" = Table.TransformColumnTypes(#"Added Custom",{{"Custom", Int64.Type}}),
#"Added Custom1" = Table.AddColumn(#"Changed Type2", "Custom.1", each [Column1.5]/[Custom]),
#"Added Custom2" = Table.AddColumn(#"Added Custom1", "Custom.2", each List.Generate(()=> [Custom],each _> 0, each _ -1)),
#"Expanded Custom.2" = Table.ExpandListColumn(#"Added Custom2", "Custom.2"),
#"Changed Type3" = Table.TransformColumnTypes(#"Expanded Custom.2",{{"Custom.2", Int64.Type}, {"Custom.1", type number}}),
#"Added Custom3" = Table.AddColumn(#"Changed Type3", "Custom.3", each Date.AddDays([Column1.2],-[Custom.2])),
#"Changed Type4" = Table.TransformColumnTypes(#"Added Custom3",{{"Custom.3", type date}}),
#"Reordered Columns" = Table.ReorderColumns(#"Changed Type4",{"Custom.3", "Column1.1", "Column1.2", "Column1.3", "Column1.4", "Column1.5", "Custom", "Custom.1", "Custom.2"}),
#"Removed Columns" = Table.RemoveColumns(#"Reordered Columns",{"Column1.1", "Column1.2", "Column1.5", "Custom", "Custom.2"})
in
#"Removed Columns"

If this post is useful to help you to solve your issue consider giving the post a thumbs up 

 and accepting it as a solution !