Forum Discussion

tangerinemdr15's avatar
tangerinemdr15
Helper I
5 years ago

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!

 

 

Need Help
Tutorial Requests