DAX of YTD and LYTD based on Fiscal year 1st Sept to 31st Aug

[anandsoftweb]

Hello Folks,

I am looking for YTD and LYTD DAX based on define fiscal year

E.g,Current year  YTD (Fiscal Year) :- 1st Sept 2017 - 31 Aug 2018 Sum of Sales data

Last tear LYTD (Fiscal Year) :- 1st Sept 2016 - 31st Aug 2017 Sum of Sales data

Thanks In Advance

[v-sihou-msft]

@anandsoftweb

In DAX, you can specify the year end date in TOTALYTD() function. 

YTD =TOTALYTD(SUM(InternetSales_USD[SalesAmount_USD]),DateTime[DateKey], ALL('DateTime'), "6/30") 

For last year YTD, you can add SAMEPERIODLASTYEAR() in CALCULATE().

LYTD =
CALCULATE (
    TOTALYTD (
        SUM ( InternetSales_USD[SalesAmount_USD] ),
        DateTime[DateKey],
        ALL ( 'DateTime' ),
        "6/30"
    ),
    SAMEPERIODLASTYEAR ( DateTime[DateKey] )
)

Regards,

[anandsoftweb]

@v-sihou-msft

LYTD is not giving the correct value while YTD is working fine.

Thanks

[anandsoftweb · Accepted solution]

LYTD = 
VAR DataMaxDate = CALCULATE ( MAX ( Data[Date] ), ALL ( Data ) ) 
RETURN 
CALCULATE ( [YTD], 
SAMEPERIODLASTYEAR 
( 
DATESBETWEEN ( Date[Date], BLANK (), DataMaxDate ) 
)
)

This above DAX is working fine...

[Josh_BI_UK]

@anandsoftwebjust looking at your solution, are you able to break it down for me so I understand how apply to my use case? Any layman's explanation of how time intelligence works for non-standard calendar would be great.

Our business runs from : 1st April yyyyy to the 31st March. I would like to compaire YOY and YTD, and most of the other date variants:
Month Vs. Same month last year
QTR Vs. Same QTR last year (p.s our Quarters are marked as follows Q1 Apr to Jun, Q2 Jul to Sep, Q3 Oct to Dec ...... etc.)
And Finally Week Vs. Same Week last year.


I have a calender table with this all mapped out.... the [Date] start from 01/01/2011 and runs on untill 31/12/2038.
I have columns which map our business variants..... e.g Quarter Number = [Business Quarter] ; [Business Year Start Date] = 01/04/yyyyy and [Business Year End Date] = 31/03/yyyy.

[richbenmintz]

Hi @Josh_BI_UK,

Hi Below you will find a link to a great time intelligence post by Marco Russo and Alberto Ferrari, two of the best!

dax time-pattern

[Josh_BI_UK]

Oh wow, very comprehensive. Might take while for me to get my head around all of it, but it's certainly covers the bulk of day, month, quarter, and year comparisons type functions one might have using DAX formulas. Big thanks to @richbenmintz and of course Marco Russo and Alberto Ferrari.

[anandsoftweb]

@v-sihou-msft Thanks for your reply will check that...

[richbenmintz]

Hi Below you will find a link to a great time intelligence post by Marco Russo and Alberto Ferrari, two of the best!

dax time-pattern

[CahabaData]

not clear where you are now as a starting point, i.e. have you set up a Date table that includes column(s) for your organization's fiscal year values?

[anandsoftweb]

@CahabaData @richbenmintz

Well I am finding Year to date sales based on Fiscal year (1st Sept 2016 to 31st Aug 2017) not (1st Jan 2017 to 31st Dec) and

LYTD (1st Sept 2015 to 31st Aug 2016)

So here Todays date is 30th Aug 2017 then I would like to calculate

Sales YTD From date 1st Sept to 2016 to 30 Aug 2017

and LYTD From date 1st Sept to 2015 to 30 Aug 2016

Now I think its more clear so if you have any idea pls provide some dax ref or calculation.

Thanks

[richbenmintz]

@anandsoftweb,

Did you reiview the post in the link i provided, it is an excellent resource for this problem. without knowing what your date dimension and model looks like it is not very easy to provide you with a formula