Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
texmexdragon2
Helper V
Helper V

Deferred Revenue

Hello Community  -  I have a situation where my accounting team wants to see (in a matrix table), our combined deferred and undefered revenue.   Right now I have a measure for shipped revenue which is just a simple SUM of shipped price.   

 

I also have a measure that just calculates the "deferred revenue".   Essentially, this is the date the revenue was actually recognized based on proof of delivery (and accounting rules).   That is the measure below. 

 

My challenge is that if I put the Accounting measure below into a matrix table, it no longer shows the other dates (it only months from the date table that have a Revenue Date).   This measure has been used in the table on the right. 

 

On the other hand, if I just use my Shipped Revenue measure (left table), it of course captures the original ship date, which is not the actual date that the revenue was recognized which is what Accounting looks at. 

 

I'm looking to create/modify a measure that allows both of these values to co-exist, without double counting them.   Any ideas?  I tried creating two different VAR statement, one for accounting revenue and one for shipped revenue, along with some conditional logic but could not work out the DAX.    Any help is appreciated!

 

Accounting Shipped Revenue =
//Includes Deferred Revenue//
CALCULATE (
    [Shipped Revenue],
    USERELATIONSHIP ( ShippedOrdersALL_Query[RevenueDate], 'Dim_Date Table'[Date])
)
 
texmexdragon2_0-1660924214784.png

 

3 REPLIES 3
v-binbinyu-msft
Community Support
Community Support

Hi @texmexdragon2 ,

In order to better understand your demands and give the right solution, could you please provide some more specific information? such as your desensitized example data and a screenshot of your desired results?

Thanks for your efforts & time in advance.

 

Best regards,
Community Support Team_ Binbin Yu

amitchandak
Super User
Super User

@texmexdragon2 , with common date and account tables, you can use both measures together.

 

You need to use the period from date columns

@amitchandak   Yes, that does work (see below), but is ultimately not what I am hoping to achieve. 

 

My ideal scenario would be to have another Measure that has the logic built in from the Accounting Shipped Revenue measure, that still shows all of the months  (not just the months that have a Revenue Date).   In other words, something like an ALL function that still allows for all dates to be shown.   Right now if I just were to use the Accounting Rev measure, it will only show values that have a Revenue Date.   Ideally there would be logic that states "if deferred equals true" show the deferred value based on the revenue date, and if false, then show the shipped value (based on shipped date).    This would have to be at the Order Line level, since an order can have multiple lines...some deferred, some not.  As is the case below.  Line one technically did ship in April, but because it was only received by the customer in May, the revenue was deferred until May. 

 

texmexdragon2_0-1660927780218.png

 

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.