Forum Discussion

Lio123's avatar
Lio123
Icon for Advocate I rankAdvocate I
16 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
johnt75's avatar
johnt75
Icon for Super User rankSuper User
16 days ago

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.

  • Lio123's avatar
    Lio123
    Icon for Advocate I rankAdvocate I
    16 days ago

    johnt75​ I have dim tables for customers, date and fact with customer, MRC amount, MRC billing start date and contract period. Now I want to calculate the total MRC for each month. 

    • johnt75's avatar
      johnt75
      Icon for Super User rankSuper User
      16 days ago

      That sounds like a good set up. Use the fact table as the basis to generate a new fact table, linked to the same dimensions, with one entry per month per customer for the duration of the contract period.