cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
codyraptor Regular Visitor
Regular Visitor

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.

 

1 ACCEPTED SOLUTION

Accepted Solutions
codyraptor Regular Visitor
Regular Visitor

Re: Deferred Revenue Model 12 months

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 8
Moderator v-yuezhe-msft
Moderator

Re: Deferred Revenue Model 12 months

@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

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
codyraptor Regular Visitor
Regular Visitor

Re: Deferred Revenue Model 12 months

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

Highlighted
codyraptor Regular Visitor
Regular Visitor

Re: Deferred Revenue Model 12 months

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???


@v-yuezhe-msft 
Moderator v-yuezhe-msft
Moderator

Re: Deferred Revenue Model 12 months

@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

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
codyraptor Regular Visitor
Regular Visitor

Re: Deferred Revenue Model 12 months


@v-yuezhe-msft 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.Capture.JPG

 

 

 

 

 

 

 

 

 

 

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

 

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

 

 

 

 

 

 

 

 

 

 

 

codyraptor Regular Visitor
Regular Visitor

Re: Deferred Revenue Model 12 months


@codyraptor wrote:

@v-yuezhe-msft  

 


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 Regular Visitor
Regular Visitor

Re: Deferred Revenue Model 12 months

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.  

pharpala Visitor
Visitor

Re: Deferred Revenue Model 12 months

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

Helpful resources

Announcements
Virtual Launch Event

Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

Power BI Helps Homeless and Trouble Youth

Power BI Helps Homeless and Trouble Youth

We spoke with Power BI Super User, Greg Deckler, about his charity work

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Users Online
Currently online: 104 members 1,729 guests