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 PBI Experts,
I had to create measure, which shows distribution of revenue depending on duration of project. I did it...but after that year slicer appeared and my measure is hardcoded only for 2017. I have no idea how to modify it to be independent of manual year input.
Simple data from my table:
billing start date new_runrate duration UDP:
1/1/2017 20,000.00 5
2/5/2017 34,000.00 3
4/13/2017 45,000.00 4
How measure shoul behave: first project started in Jan and it's duration is 5 month, another project started in Feb .
Revenue for Jan = 20K
for Feb - 20K for Feb from 1 project + 34K for Feb from second project = 54K
for March = 54K
for April 20 + 34 + 45=99
Measure for 2017:
year_billing = CALCULATE(SUM(opportunity[new_runrate]),
FILTER(opportunity,opportunity[billing_start_date]
<= CALCULATE(MAX(cal[calendar_date].[Date]))),
FILTER(opportunity,IF(opportunity[duration_UPD]=1,
opportunity[billing_start_date],
IF(opportunity[billing_start_date]+(opportunity[duration_UPD]*31)
> DATE(2017,12,31),DATE(2018,1,1),
opportunity[billing_start_date]+(opportunity[duration_UPD]*31)))
>= CALCULATE(MIN(cal[calendar_date].[date]))))
P.S. "calendar date" includes all dates for 3 years 2016-2018
Can someone help me with this?
Solved! Go to Solution.
Hi @bsas,
You can create a measure:
year_billing = CALCULATE(SUM(opportunity[new_runrate]),
FILTER(opportunity,opportunity[billing_start_date]
<= CALCULATE(MAX('Calendar'[Date]))),
FILTER(opportunity,IF(opportunity[duration_UPD]=1,
opportunity[billing_start_date],
IF(opportunity[billing_start_date]+(opportunity[duration_UPD]*31)
> DATE(YEAR(MAX('Calendar'[Date])),12,31),DATE(YEAR(MAX('Calendar'[Date]))+1,1,1),
opportunity[billing_start_date]+(opportunity[duration_UPD]*31)))
>= CALCULATE(MIN('Calendar'[Date]))))
Please see attached pbix file.
Best Regards,
Qiuyun Yu
Hi @bsas,
You can create a measure:
year_billing = CALCULATE(SUM(opportunity[new_runrate]),
FILTER(opportunity,opportunity[billing_start_date]
<= CALCULATE(MAX('Calendar'[Date]))),
FILTER(opportunity,IF(opportunity[duration_UPD]=1,
opportunity[billing_start_date],
IF(opportunity[billing_start_date]+(opportunity[duration_UPD]*31)
> DATE(YEAR(MAX('Calendar'[Date])),12,31),DATE(YEAR(MAX('Calendar'[Date]))+1,1,1),
opportunity[billing_start_date]+(opportunity[duration_UPD]*31)))
>= CALCULATE(MIN('Calendar'[Date]))))
Please see attached pbix file.
Best Regards,
Qiuyun Yu
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 |
---|---|
111 | |
94 | |
80 | |
68 | |
59 |
User | Count |
---|---|
150 | |
119 | |
104 | |
87 | |
67 |