Forum Discussion

Lio123's avatar
Lio123
Advocate I
11 hours ago

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 month. For example, Customer A starts billing in January and pays $100 every month until the contract ends, while Customer B starts billing in March and pays $50 every month until the end of the contract.

So I want see how much money we have received every month and from which customers.
Something like this 
Jan - 100
Feb - 100
Mar - 100+50 
Apr - 100+50
.... so on

Note : I don't have the end of the MRC month date but I have the start of the MRC billing and number of billable months in each year

please help with dax

DAX

4 Replies

  • johnt75's avatar
    johnt75
    Super User
    11 hours 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
      Advocate I
      11 hours 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
        Super User
        9 hours 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.