How to show Annual YTD Plan Numbers when Dataset is Full Year

[Shelley]

We have a Sales Plan by month for the full fiscal year. When I use the YTD Function it takes the data for the whole year. This is okay if I want to show it by month, but our teams need to compare YTD Plan and Actuals for the total YTD - as well as with Last Year.

Here are examples of my formulas:

YTD Total Orders AOP = TOTALYTD([Total Orders AOP], 'RA_Daily_Calendar'[Date], "9/30")
YTD Total Revenue AOP = TOTALYTD([Total Revenue AOP], 'RA_Daily_Calendar'[Date], "9/30")

YTD Total Revenue Actual = TOTALYTD([Total Revenue Converted], 'RA_Daily_Calendar'[Date], "9/30")

Prior YTD Total Revenue = CALCULATE([YTD Total Revenue Converted], SAMEPERIODLASTYEAR(RA_Daily_Calendar[Date]))

When I show by month and year it is good:

However, I need to show Current YTD figures only, like this:

YTD Actuals show correct YTD because that's the only data there is. So, is there a simple way to accomplish the PLAN and PRIOR Year totals for YTD only. I created a YTD flag on my calendar, and can use this as a filter on the visual or the expression, but then it doesn't work for Prior Year because I cannot figure out how to create the correct flag.

Please help if you can. Thanks!

[Ashish_Mathur]

Hi,

Share the link from where i can download your PBI file.

[MFelix]

Hi @Shelley,

Not really sure what you want to achieve and what's the error you are getting.

Can you provide some sample data and the result you are getting vs what you need to have,

Another question this is to use on PBI or Power Query excel, asking this because your images look like excel based.

Regards,

MFelix

[Shelley]

Hello @MFelix and @Ashish_Mathur,

This is a late reply, but this is still an issue for me. I am using Power BI - just used Excel for the images above as our actual data is confidential (which also makes it difficult for me to create a simple PBIX file to share with you). The TOTALYTD Function works fine for starting the summation at the fiscal year, but what I find is it is no good for an end date of YTD. For example, let's say we have a fiscal year that ends on 9/30, so October begins the summation. Let's say we have now closed the books on March sales. So, TOTALYTD for actuals for the current year and prior year work with these expressions:

YTD Total Revenue Actual = TOTALYTD([Total Revenue Converted], 'RA_Daily_Calendar'[Date], "9/30")

Prior YTD Total Revenue = CALCULATE([YTD Total Revenue Converted], SAMEPERIODLASTYEAR(RA_Daily_Calendar[Date]))

This seems to work because there is NO more data for actuals after March in the current year.

However, we have plan (AOP) numbers loaded for the full year. If I use similar formulas for summing the AOP plan YTD, it again begins summing in October, but sums the FULL year, thru September because there is plan data in the data table. For example, this expression returns the entire year's plan.

YTD Total Revenue AOP = TOTALYTD([Total Revenue AOP], 'RA_Daily_Calendar'[Date], "9/30")

So, in my table above, the Revenue Plan total, using this expression is 1195, but it should instead show 595.

I've worked my way around this using a number of methods, but nothing seems ideal. I'm wondering how you or others find the best way to handle these situations?

Here are some expressions I've used:

FYTD Revenue AOP = 
    IF(MAX('RA_Daily_Calendar'[Date]) > TODAY(), BLANK(), CALCULATE(TOTALYTD([Total Revenue AOP], 'RA_Daily_Calendar'[Date], ALLSELECTED(RA_Daily_Calendar[Date]),"9/30")))

In another application, I've put a YTD Flag in my calendar:

Flag: YTD =
    IF('RA_Daily_Calendar'[Fiscal_Month] <=
        LOOKUPVALUE('RA_Daily_Calendar'[Fiscal_Month], RA_Daily_Calendar[Date], MAX('Actual Revenue Table'[Posting_date])),
            1,0)

And then used it in the formula as a filter:

FYTD Revenue AOP = 
    'RA_Daily_Calendar'[Date], ALLSELECTED(RA_Daily_Calendar[Date]),"9/30")))
   TOTALYTD(SUM('AOP'[AOP Revenue Value]), 'RA_Daily_Calendar'[Date], 'RA_Daily_Calendar'[Flag: YTD] = 1, "09/30") 

Any thoughts and ideas are appreciated. These YTD expressions seem to cause me so much consternation. I just can't find an approach that I'm confident in using consistently. Thanks!

[Ashish_Mathur]

Hi,

What result do you get when you try this

YTD Total Revenue AOP = TOTALYTD([Total Revenue AOP], 'RA_Daily_Calendar'[Date], "9/30")-CALCULATE([Total Revenue AOP],'RA_Daily_Calendar'[Date]>TODAY())