Deferred Revenue - Help with DAX Please!

[tangerinemdr15]

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!

[littlemojopuppy]

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]
    )

[littlemojopuppy]

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

[tangerinemdr15 · Accepted solution]

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

[littlemojopuppy]

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

[Greg_Deckler]

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

[tangerinemdr15]

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