Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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]))))
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
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"?
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
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
98 | |
97 | |
81 | |
77 | |
66 |
User | Count |
---|---|
126 | |
105 | |
103 | |
81 | |
72 |