Dax formula Help

[Anonymous]

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.

[Anonymous]

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

MRR to Show = DIVIDE([MRR], COUNTROWS(VALUES(Table[Month])))

[Anonymous]

@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]

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.

[Anonymous]

https://www.dropbox.com/s/24q1kssixwmfd8f/Dummy%20Data.xlsx?dl=0 i understand what you are saying the issue is when we are looking at a contract that is 160 months long may be an issue. Further to this what field would i use as a date picker in power bi? as i would have multiple columns with multiple dates

[Anonymous]

160 lines is not a problem for Power BI, and Power Query will do the job automatically.

You have a start date and a duration. With the List.Dates function you can easily create a line for each day and a new column with the date. This new date column is the one you have to relete to your date table. The problem here is you have to create only one line for each month. I have done that before but I don't remember in which project, it will take me a while to find out and send the M code.

[Anonymous]

if you could, that would be a massive lifesaver. Whats the quickest way for me create a line for each month with the new date so if its 8 months i would need the start date 31/08/18 30/09/2018 31/10/2018 etc?