Remove future date in totalytd or datesytd

[kennyyip]

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

[danextian · Accepted solution]

Hi @kennyyip

You can try this:

=
IF ( NOT ( ISBLANK ( [Revenue Measure] ) ), YTD Measure Formula )

[kennyyip]

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

[kennyyip]

Problem solved. Sorry for asking stupid question