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.