Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
8 years ago

Dax formula Help

Am looking for a formula for the following. Customer x order 1 product but is paid over 8 months from January to August. Rather an just giving me the sum of the product for the full 8 months i want to be able to look at the MRR but when i filter to January Febuary and March the MRR is * by 3 as it is looking at 3 months. Currently i do this with excel but have multiple columns for month which is a huge headache when looking at 10 years worth of data. Any help would be amazing.

Need Help

6 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    8 years ago

    You can divide the MRR you have by the number of Months selected:

     

    MRR to Show = DIVIDE([MRR], COUNTROWS(VALUES(Table[Month])))
    • Anonymous's avatar
      Anonymous
      Not applicable
      8 years ago

      Anonymous i want to be able to add a date filter to my power bi report but obviously if i filter to look at jan to march i need to include the MRR 3 times not just once. Obviously each MRR will only have a start date so would need to be based on the numbers of months the subscription is for?

      • Anonymous's avatar
        Anonymous
        Not applicable
        8 years ago

        I would need a data sample to see the best way.

        If you have a start date and an end date (or duration) pfor each MRR, maybe the easiest way (it may not be the best) is to "duplicate" lines in Power Query and have one line for each Month the MRR is "active". In every line you calculate the proportional part of the MRR so when you filter several months, you will have the corresponding amount.

         

        Not sure if I've been clear enough.