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!
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?
@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.