Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hi,
I have a formula below calculating YTD run-rate which is YTD revenue over YTD budget.
YTD Run-rate = if(lastdate(T_Revenue[Revenue_Date])<=date(2018,3,31),
divide(totalytd(sum(T_Revenue[Revenue_Actual]),T_Revenue[Revenue_Date].[Date]),
totalytd(sum(T_Revenue[Revenue_budget]),T_Revenue[Revenue_Date].[Date])),
blank())
My data set is from 2017 till 31/3. However, the measure calculates values for 4/2018 till end of 2018, locked the value as of YTD 31/3/2018. So i tried to use IF and LASTDATE to remove the value of future date but in vain. I've tried both DATESYTD and TOTALYTD, or change the condition to max(T_Revenue[Revenue_Date])<=date(2018,3,31)...all are unsuccessful. I checked the old post but seems no solution works. Please help. Thanks a lot.
Line chart belows show the value of YTD Run-rate...the flat part is the future value i want to remove. Bar chart is the monthly revenue
Solved! Go to Solution.
Hi @kennyyip
You can try this:
= IF ( NOT ( ISBLANK ( [Revenue Measure] ) ), YTD Measure Formula )
Proud to be a Super User!
Hi @kennyyip
You can try this:
= IF ( NOT ( ISBLANK ( [Revenue Measure] ) ), YTD Measure Formula )
Proud to be a Super User!
Hi @danextian
My revenue is just a column of data points so ISBLANK does not work, any alternatives? Sorry I am relatively new to power BI.
Regards,
Kenny
Problem solved. Sorry for asking stupid question
User | Count |
---|---|
93 | |
85 | |
78 | |
68 | |
62 |
User | Count |
---|---|
113 | |
99 | |
96 | |
64 | |
58 |