Calculate revenue per month based on duration

[bsas]

Hi All,

I have trouble with showing revenue per month based on duration (e.g. there is 1 project with amount = 10 000 per month for 4 months started in February, another with amount = 5 000 for 2 moths started in march. I want to show: feb = 10 000, mar = 15 000, apr = 15 000, may = 10 000 june = 0. I have column with start date (e.g. 7/12/2017), duration (3, 5, 7) and revenue. 

Table with data:

Project	amount	duration	Start date
project1	10 000	4	2/1/2017
project2	5 000	2	3/13/2017

Expected result:

revenue	month
10 000	February
15 000	March
15 000	April
10 000	May
0	June

I have created measure below. I've tried to use calculate...but i have no idea how to add duration here. Could someone help me with this.

billing_1 = CALCULATE(SUM(tab[rate]),
FILTER(Filter(ALLSELECTED(tab),tab[start date]),
AND(tab[start date] <= MAX(tab[start date]),
tab[start date] >= MIN(tab[start date]))))

[v-shex-msft]

Hi @bsas,

I'd like to suggest you to use below formula to calculate the total revenue.

Measure formula:

Total revenue = SUMX(FILTER(ALL(Table),CONTAINS(CALENDAR([Start date],DATE([Start date].[Year],[Start date].[MonthNo]+[duration],[Start date].[Day])-1),[Date],MAX('Calendar'[Date]))),[amount])+0

Notice: your result not suitable for your duration. In table 1 it shows 4 and 2 as duration, so end date should be 6/1/2017 and 5/13/2017. But the result in table 2 not match the date range.

Analysis sample:

End Date = DATE([Start date].[Year],[Start date].[MonthNo]+[duration],[Start date].[Day])

Table = SUMMARIZE(ADDCOLUMNS(CALENDAR(MIN(Table1[Start date]),MAX(Table1[End Date])),"Month",FORMAT([Date],"mmmm"),"Amount",SUMX(FILTER(ALL(Table1),CONTAINS(CALENDAR([Start date],[End Date]),[Date],[Date])),[amount])+0),[Amount],[Month])

Regards,

Xiaoxin Sheng

[bsas]

Thank you @v-shex-msft for end date formula...i forgot about it. Used it in my formula (do not use your variant as I have 4 differrent categories...). 

I have one more issue that just thought about....I use formula to calculate start date:

start date = IF(AND(MONTH('tab'[closedate]+21)=1,
YEAR('tab'[closedate]+21)=2018),DATE(2017,12,1),
DATE(2017,MONTH('tab'[closedate]+21),1))

But if date is in 2018 I cahnge it to 12/1/2017 it is incorrect, How I can can remove this data in such case (not using report filter, but formula)? Should I use measure in such case and "allexept"?

[v-shex-msft]

HI @bsas,

>>for end date formula...i forgot about it. Used it in my formula (do not use your variant as I have 4 differrent categories...). 

Not very clear for your formula logic, can you please share the table structure for future analysis?

Regards,

Xiaoxin Sheng

[bsas]

2 hour fo searching and hour of brainstorming and vyalya:

Billing = CALCULATE(SUM(tab[rate]),FILTER(tab,tab[start date] 
<= CALCULATE(MAX('Calendar'[allDates].[Date]))),
FILTER(tab,IF(tab[Duration]=1,tab[start date],
IF(tab[start date]+(tab[Duration]*31)>= DATE(2017,12,31),
DATE(2017,12,31),tab[start date]+(tab[Duration]*31))) 
>= CALCULATE(MIN('Calendar'[allDates].[Date]))))

P.S. Thanks to post "How to sum values by period based on the start and end" for giving me idea how to change my thoughts to right direction.

P.P.S Posting this solumtion just because maybe it will be usefull for someone.