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

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply

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_DTBL_CHRG_AMTMonth Start DateMonth End DateBL_TYP_TXT
01/20/2020100001/01/202001/31/2020Monthly
02/27/2020100002/01/202002/29/2020Monthly
03/31/2020100003/01/202003/31/2020Monthly
04/30/202050004/01/202004/30/2020Monthly
05/28/202050005/01/202005/31/2020Monthly

 

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

 JanFebMarAprMay
01/20/202083.3383.3383.3383.3383.33
02/27/2020 166.6783.3383.3383.33
03/31/2020  250.0083.3383.33
04/30/2020   166.6741.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!

 

 

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

View solution in original post

6 REPLIES 6
littlemojopuppy
Community Champion
Community Champion

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

littlemojopuppy_1-1610119116270.png

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

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

littlemojopuppy_2-1610119403683.png

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

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

Greg_Deckler
Community Champion
Community Champion

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



Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
DAX For Humans

DAX is easy, CALCULATE makes DAX hard...

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

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

Find out what's new and trending in the Fabric community.

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.

Join our Fabric User Panel

Join our Fabric User Panel

This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.