Fiscal Year To Date calculations

[dapperscavenger]

I have a date table, it rus in monthly buckets from 01 July 2019 to 01 June 2022 (4 fiscal years, 48 rows)

My fiscal Year starts on 01 July and ends 30th June.

I have created a YTD measure:  

YTD Sales Outlook = TOTALYTD([Sales Outlook], DimDate[LongMonthYear], "06/30" )

Sales outlook is a combination of actual sales and forecasted sales. 

When I put it in a table with the Fiscal Years, it shows up correctly.

However, when I put this in a card, it gives me next year sales YTD. 

I would like to see this year's YTD

Thanks for the help!

[Angith_Nair]

Hi @dapperscavenger ,

When you put this measure in the table then that measure gets filtered by row context whereas when you put this measure in a card then the max date will be taken in the card. For this, you can have a date slicer that will slice a particular date and the results are shown from the year up to that date.

[dapperscavenger]

Thank you.

What filter do I need to use in the formua if I want to compare YTD this year with same of previous year ? 

i.e. YTD this year would be sales July to March.  What are sales July to March this year, and what are sales July to March of previous year?  Then I am comparing same period.

[Angith_Nair · Accepted solution]

@dapperscavenger  use SAMEPERIODLASTYEAR i.e 

YTD Sales Outlook = TOTALYTD([Sales Outlook], DimDate[LongMonthYear], "06/30" )
YTD Sales Outlook last year = 
CALCULATE ( 
            [YTD Sales Outlook],
             SAMEPERIODLASTYEAR ( DimDate [Date] )
)

[dapperscavenger]

Perfect, thank you!

[amitchandak]

@dapperscavenger , do you have any filter/slicer on-page. if not the last date of the calendar will be taken as the last date.

Please refer to my video on why TI fails: https://www.youtube.com/watch?v=OBf0rjpp5Hw

[dapperscavenger]

No, I don't want to have to use manual slicers for this one, or else I would have to remember to mantain it all the time 🙂

So, to get the previous fiscal year, I would have to do some sort of datadd -1 year I think.

And to show only YTD, I would have to use some sort of this month criteria, rather than 06/30?

[amitchandak]

@dapperscavenger , if you do not want to select any date field in slicer, then your calendar should end on jun 30, of current year. That will take this year as end date without you selecting it