Help with DAX

Hi,

You can handle this by creating a proper Calendar/Date table and then calculating whether each customer's MRC is active for the selected month.

Assuming your customer table has something like:

• Customer
• MRC Amount
• MRC Start Date
• Billable Months

You can first create a measure like this:

Monthly MRC = VAR CurrentMonthStart = DATE( YEAR(MAX('Calendar'[Date])), MONTH(MAX('Calendar'[Date])), 1 ) VAR CurrentMonthEnd = EOMONTH(CurrentMonthStart, 0) RETURN SUMX( Customers, VAR StartDate = Customers[MRC Start Date] VAR BillableMonths = Customers[Billable Months] VAR EndDate = EDATE(StartDate, BillableMonths) - 1 RETURN IF( StartDate <= CurrentMonthEnd && EndDate >= CurrentMonthStart, Customers[MRC Amount], 0 ) )

Then put Calendar[Month] or Calendar[Month Year] on the rows/axis of your visual and use Monthly MRC as the value.

For example:

Customer A contributes $100 starting in January, while Customer B contributes $50 starting in March. The measure checks whether each customer's contract is active during the selected month and adds the applicable MRC.

If you also want to see the amount by customer

Put:

Customer → Rows
Calendar[Month Year] → Columns
Monthly MRC → Values

This will allow you to see which customers are contributing to the MRC in each month.

One thing to watch for: if Billable Months represents the number of months from the MRC start date (rather than an annual value), the EDATE() calculation above should work directly. If "billable months in each year" means something different—for example, 12 billable months every year until a separate contract end—then the logic would need to be adjusted.

Also make sure your Calendar table is marked as a Date table and has a continuous range covering all of the MRC periods.

hi Lio123​ 

As you have described, i tried to solve your problem with the generated data with dax and solved the problem. please check whether this helps or not? for any further query please reply to this post. 

If this solves your problem, please mark this as solution and give a kudos. 

Hi Lio123​,

Since you already have Customer, MRC Amount, MRC Start Date and Contract/Billable Months, I tried to come up with a solution for your question. Please find the atatched pbix file and let me know if it doesn't help.

I would create a monthly fact table with one row for every billable month of each customer contract. And then a create measure Total MRC = SUM ( FACT_MRC_MONTHLY[MRCAmount] )

If this reply helped solve your problem, please consider clicking "Accept as Solution" so others can benefit too. And if you found it useful, a quick "Kudos" is always appreciated, thanks!

Best Regards,
Maruthi
LinkedIn - http://www.linkedin.com/in/maruthi-siva-prasad/
X            -  Maruthi Siva Prasad - (@MaruthiSP) / X

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.

I wouldn't solve this problem just in DAX, I'd create a new table containing customer, date and amount, with a row for each month a customer has a MRC. You could generate that in Power Query or SQL, and then the DAX measure becomes a simple SUM over the new table.

Having an entry per customer per month means that you would be able to show which customers contributed to a given month.