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,

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

codyraptor's avatar
codyraptor
Resolver I
7 years ago
Anonymous wrote:

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

 

Hey Lydia....I've added a dimension (Channel) to your model to show you want I mean.  The 1st issue is the total is not rolling up properly.  See in screenshot below...total is showing $50 for Jan...should be $100.  Taking the 'max' of the row is not allowing the amounts to be dynamic and sum up properly when adding dimensions.

 

 

 

 

 

 

 

 

 

 

I've also added the PBIX file below in One Drive.

 

https://1drv.ms/u/s!AkORWA1nfYaEfM7Zb8hA8299fnA

 

 

 

 

 

 

 

 

 

 

 

  • codyraptor's avatar
    codyraptor
    Resolver I
    7 years ago
    codyraptor wrote:
    Anonymous  

     

    I see what it's doing now.  It's actually taking the Rev when I create the new table and applying it to each 'day' in the dates.  I only need my dates to reflect the month...so just 1/1/2017 for example.  I need the dates to reflect a single day and that would fix it.  Thoughts?

    • codyraptor's avatar
      codyraptor
      Resolver I
      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

    Why doesn't he row  total give the cumalative total value in this ? any idea?