cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
dapperscavenger
Helper IV
Helper IV

Fiscal Year To Date calculations

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!

1 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] )
)

View solution in original post

7 REPLIES 7
Angith_Nair
Resolver II
Resolver II

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.

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.

@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] )
)

View solution in original post

Perfect, thank you!

amitchandak
Super User IV
Super User IV

@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



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

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?

 

@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



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

MBAS Attendee Badge

Claim Your Badge & Digital Swag!

Check out how to claim yours today!

Top Solution Authors
Top Kudoed Authors