Deferred Revenue Model

[NWBI]

Hello all,

I am building a model to calculate and allocate deferred revenue balances for our accounting team.

I need to take each annual payment, divide that revenue over 12 months, and assign that amount to each following month for 12 months after payment. I have isolated all annual payments in my data, and I have created a relationship between a date table and Date of Payment [Column1]. My data in use looks somthing like this below:

Column1

Date of Payment

Column2

Transaction amount

Column 3

Defferal period end date (12 months from [Column1])

Column 4

Monthly Deferral Amount ([column2]/12)

I want to create a simple bar graph which assigns total amount to be deferred [Column4] by month looking forward the next 12 months. New sales are occuring daily and thus this is a constatnly changing value. I used the Dax code below to create a measure:

Defferred Revenue Schedule = 
CALCULATE(SUM('Staging vTransactions'[Monthly Deferral Amount]), 
Filter(
ALL('Staging vTransactions'[Deferral Period End Date]),
'Staging vTransactions'[Deferral Period End Date]>=max(Dates[Date])))

But I cannot get my bar graph to display the deferred revenue amount past the current month, as seen below:

How to I get the deferred revenue amount (1/12 of original payment) to display on each following month, for only the 12 months directly following sale????

Much thanks ahead of time  

[NWBI · Accepted solution]

no responses...so sad...good thing I am amazing and ended up solving it myself. 

eliminate the relationship between the date table and the transaction table (original relationship was between date table[Date] & transactiontable[date of transaction]). Create a filter using the date field in the date table to select desired range for bar graph. All 12 months of the deferral period should display amount to be deferred....

thank you me