topic Re: Deferred Revenue - Help with DAX Please! in DAX Commands and Tips

Deferred Revenue - Help with DAX Please!

tangerinemdr15 — Fri, 08 Jan 2021 14:28:09 GMT

Hi! I need to recognize the Charge Amount in Appropriate month evenly throughout the year.

Here is an example of the raw data:

RIS & RAL Annual Bill + RIS Monthly
BL_DT	BL_CHRG_AMT	Month Start Date	Month End Date	BL_TYP_TXT
01/20/2020	1000	01/01/2020	01/31/2020	Monthly
02/27/2020	1000	02/01/2020	02/29/2020	Monthly
03/31/2020	1000	03/01/2020	03/31/2020	Monthly
04/30/2020	500	04/01/2020	04/30/2020	Monthly
05/28/2020	500	05/01/2020	05/31/2020	Monthly

So far I have been able to calculate the red amounts with the following expression:

	Jan	Feb	Mar	Apr	May
01/20/2020	83.33	83.33	83.33	83.33	83.33
02/27/2020		166.67	83.33	83.33	83.33
03/31/2020			250.00	83.33	83.33
04/30/2020				166.67	41.67
05/28/2020					208.33

RIS Monthly Arrival = sumx(FILTER('RIS & RAL Annual Bill + RIS Monthly', 'RIS & RAL Annual Bill + RIS Monthly'[BL_DT] >= 'RIS & RAL Annual Bill + RIS Monthly'[Month Start Date] && 'RIS & RAL Annual Bill + RIS Monthly'[BL_DT] <= 'RIS & RAL Annual Bill + RIS Monthly'[Month End date] && 'RIS & RAL Annual Bill + RIS Monthly'[BL_TYP_TXT] <> "Annual"), 'RIS & RAL Annual Bill + RIS Monthly'[BL_CHRG_AMT]*(month('RIS & RAL Annual Bill + RIS Monthly'[BL_DT])/12))

I cant figure out the black values with DAX (the amounts presented are what it should come out to, calculated in excel).

I tried this expression but it comes up blank. I think the issue is the bolded portion:

RIS Monthly Deferred = sumx(FILTER('RIS & RAL Annual Bill + RIS Monthly', 'RIS & RAL Annual Bill + RIS Monthly'[BL_DT] < Month( 'RIS & RAL Annual Bill + RIS Monthly'[Month Start Date]) && 'RIS & RAL Annual Bill + RIS Monthly'[BL_TYP_TXT] <> "Annual" && YEAR('RIS & RAL Annual Bill + RIS Monthly'[BL_DT])=YEAR('RIS & RAL Annual Bill + RIS Monthly'[Month Start Date])), 'RIS & RAL Annual Bill + RIS Monthly'[BL_CHRG_AMT]*(1/12))

Thank you in advance!

Re: Deferred Revenue - Help with DAX Please!

Greg_Deckler — Fri, 08 Jan 2021 14:40:38 GMT

tangerinemdr15 - What is the logic for the black amounts? Sorry, I'm not understanding how those amounts should be calculated.

Re: Deferred Revenue - Help with DAX Please!

tangerinemdr15 — Fri, 08 Jan 2021 15:00:09 GMT

Greg_Deckler

Hi! the logic for the black amounts is to take 1/12th of the billing charge amount for the remaining months of the year. For example, the charge that came in on 3/31/20 for $1000. $250 of it was recognized in March. $1,000 * (3/12)= $250. From April through the rest of the year, 1/12th will be recognized each month.

$1,000 * (1/12) = $83.33

Thank you in advance for taking a look!

Re: Deferred Revenue - Help with DAX Please!

littlemojopuppy — Fri, 08 Jan 2021 15:19:59 GMT

Hi tangerinemdr15

I think I've got this...this is the kind of thing that makes my former accountant self happy 🙂

As a check, a created a table with all the amounts you have above...

The DAX for that table follows...

SUMMARIZE( 'Calendar', 'Calendar'[Year], 'Calendar'[Month], "NewBilling", CALCULATE( [Billing], 'Raw Data'[BL_TYP_TXT] = "Monthly" ), "MonthlyBilling", CALCULATE( [Billing] / 12, 'Raw Data'[BL_TYP_TXT] = "Monthly" ), "ImmediatelyRecognized", CALCULATE( ([Billing] / 12), 'Raw Data'[BL_TYP_TXT] = "Monthly" ) * 'Calendar'[Month] )

Re: Deferred Revenue - Help with DAX Please!

littlemojopuppy — Fri, 08 Jan 2021 15:24:27 GMT

tangerinemdr15 here's your measure. It assumes you have a date table and it is marked appropriately...

Billing = SUM('Raw Data'[BL_CHRG_AMT]) Monthly Billing Amount = CALCULATE( [Billing] / 12, FILTER( 'Raw Data', 'Raw Data'[BL_TYP_TXT] = "Monthly" ) )

The measure for [Billing] is also used in the check table above.

Re: Deferred Revenue - Help with DAX Please!

tangerinemdr15 — Fri, 08 Jan 2021 20:56:53 GMT

littlemojopuppy

Thank you so much for taking the time to help me! With your help, I was able to figure out the calculate function and It was helpful to make that new "Monthly Billing" measure. This is where I landed:

RIS Monthly Deferred = (TOTALYTD(CALCULATE('RIS & RAL Annual Bill + RIS Monthly'[MonthlyBilling2]/12),PREVIOUSMONTH('RIS & RAL Annual Bill + RIS Monthly'[Month Start Date])))

Thank you so much!

Re: Deferred Revenue - Help with DAX Please!

littlemojopuppy — Fri, 08 Jan 2021 20:59:41 GMT

tangerinemdr15 you're welcome...glad I could help! My inner accountant enjoyed it! 🙂