Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and a 50 percent discount on exams.
Get startedEarn 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