Forum Discussion

tangerinemdr15's avatar
tangerinemdr15
Icon for Helper I rankHelper I
5 years ago
Solved

Deferred Revenue - Help with DAX Please!

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_A...
Help
new user
  • tangerinemdr15's avatar
    tangerinemdr15
    5 years ago

    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's avatar
littlemojopuppy
Icon for Community Champion rankCommunity Champion
5 years ago

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's avatar
littlemojopuppy
Icon for Community Champion rankCommunity Champion
5 years ago

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's avatar
    tangerinemdr15
    Icon for Helper I rankHelper I
    5 years ago

    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's avatar
      littlemojopuppy
      Icon for Community Champion rankCommunity Champion
      5 years ago

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