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 defer that revenue for 12 months.   This seems like it should be simple.  I can't seem to get it resolved.  Help is greatly appreciated!!!

 

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

 

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.  

8 Replies

  • 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...

    • codyraptor's avatar
      codyraptor
      Resolver I
      7 years ago

      I was able to get the table to work.  However, it does not seem to calculate properly when you have more dimensions.

       

      Another words...I need to pull revenue over..but be able to slice it by State, Company, Channel, etc....   Pulling the 'Max' value doesn't seem to allow it to be dynamic.

       

      Any suggestions???

      Anonymous 
      • Anonymous's avatar
        Anonymous
        Not applicable
        7 years ago

        codyraptor,

        You would need to bring the dimensions in the new table. Please share sample data of your table or your PBIX file here.

        Regards,
        Lydia