Forum Discussion

Lio123's avatar
Lio123
Icon for Advocate I rankAdvocate I
23 days ago
Solved

Help with DAX

Dear Pros, Need help here I have a monthly recurring charge (MRC) from multiple customers that continues every month until the end of their contracts. I want to calculate the MRC amount for each ...
DAX
techies's avatar
techies
Icon for Super User rankSuper User
21 days ago

Hi Lio123​ 

You can materialize it as a calculated table like this:

Bridge_MRC = SELECTCOLUMNS(     GENERATE(         Fact_MRC,         VAR NumMonths = Fact_MRC[ContractMonths]         VAR MonthStart = DATE( YEAR( Fact_MRC[MRCStartDate] ), MONTH( Fact_MRC[MRCStartDate] ), 1 )         RETURN             ADDCOLUMNS(                 GENERATESERIES(0, NumMonths - 1, 1),                 "BillingMonth", EDATE(MonthStart, [Value])             )     ),     "CustomerID", Fact_MRC[CustomerID],     "MRCAmount", Fact_MRC[MRCAmount],     "BillingMonth", [BillingMonth],     "MonthYearSort", YEAR([BillingMonth]) * 100 + MONTH([BillingMonth]) )

 

one row per customer per billed month, using ContractMonths to drive the expansion, then relate BillingMonth to your Date table and just SUM() in the measure.