Forum Discussion

codyraptor's avatar
codyraptor
Resolver I
7 years ago
Solved

Deferred Revenue Model 12 months

I am struggeling on this one.  I'm trying to defer revenue for 12 months. Example: Sale Date = Jan 17 Revenue = 1200 Defer results: Jan = 100 Feb = 100 Mar = 100 etc.....   It should only de...
Need Help
  • codyraptor's avatar
    codyraptor
    7 years ago

    I figured it out I think.  These results are giving me what I expected.

     

    Table = GENERATE(
    'Orig Table',
    FILTER(
    CALENDAR(MIN('Orig Table'[Sales Date]),MAX('Orig Table'[Deferred Sales Date]))
    ,[Date]>=[Sales Date] && [Date] <= [Deferred Sales Date].[Date] && DAY([Date])=1))

    This gets the data to only look at the 1st day of the month in the calendar between the dates you've generated.  

Anonymous's avatar
Anonymous
Not applicable
7 years ago

codyraptor,

Create the following columns in your table. Change data type of Start Date to Date.

Start Date = Table[Sale Date]
End date = DATE(YEAR(Table[Start Date]),MONTH(Table[Start Date])+11,DAY(Table[Start Date]))


Create a new table using DAX below.

Tablenew = 
SELECTCOLUMNS(
    GENERATE(
            'Table2',
            FILTER(
                CALENDAR(MIN('Table'[Start Date]),MAX('Table'[End date]))
                ,[Date]>=[Start Date] && [Date] <= [End date]
            )
       ),"SaleID",Table[Sale Date],"Date",[Date],"Revenue",[Revenue]/12)


Create a month column and REVENUE1 measure in the new table. For more details, please check attached PBIX file.

Month = FORMAT(Tablenew[Date],"YYYY-MMM")
REVENUE1 = MAX(Tablenew[Revenue])



Regards,
Lydia

codyraptor's avatar
codyraptor
Resolver I
7 years ago

I have tried to implement your suggestion, but I keep getting a 'not enough memory to complete this operation' error.  Any suggestions?  My dataset is about 3 years worth of data...