Financial Year starts from 1st June and ends on 31st May, how to calculate sum YTD n-1

[roysampad91]

Financial Year starts from 1st June and ends on 31st May, how to calculate sum from first day of the financial year to the last day of the previous month dynamically.

Date column is all dates

DAX:

YTD CP OER =

VAR y = TOTALYTD([XXXXX],'Date'[Date],"5/31")

var SelectedDate = SELECTEDVALUE('Date'[Date])

VAR YearStartMonth = 06  //first month of fiscal year

VAR YearStartDay = 01 //first day of fiscal year

VAR MaxDate =

    STARTOFMONTH('Date'[Date]) //last day of previous month

VAR MaxYear =

    YEAR ( MaxDate ) //year of last day of period

VAR LastYear =

    MaxYear-1

VAR YearStartDateThisYear =

    DATE ( MaxYear, YearStartMonth, YearStartDay ) //first day of calculated period

VAR YearStartDateLastYear =

    DATE ( MaxYear-1, YearStartMonth, YearStartDay ) //first day of calculated period last year

VAR YearStartDateSelected =

    IF (

        YearStartDateThisYear <= MaxDate, //if the period is month from June

        YearStartDateThisYear, //then take the beginning of period this year

        YearStartDateLastYear //otherwise take the beginning of period last year

    )

RETURN

    CALCULATE (

        [XXXXX],

        DATESBETWEEN (

            'Date'[Date],

            YearStartDateSelected,

            MaxDate

        )

    )

[OwenAuger]

Hi @roysampad91 

If I've understood you correctly, you want your measure to return the YTD value of [XXXXX] evaluated as at the end of the month prior to the filtered date (based on June-May year).

I think this can be simplified to something like:

YTD CP OER =
CALCULATE (
    [XXXXX],
    DATESYTD (
        PREVIOUSMONTH ( 'Date'[Date] ),
        "5/31"
    )
)

assuming a properly configured Date table (marked as Date table etc).

Does this give the expected result?

Regards

[roysampad91]

Will it calculate till the last day of the previous month?

[OwenAuger · Accepted solution]

@roysampad91 

Correct.

1. First, PREVIOUSMONTH shifts the date filter to the month ending just before the month of the maximum currently filtered date.
2. Then DATESYTD adjusts the Date filter to the YTD period ending on the last day of the month determined in step 1.

Here's a quick example on DAX.do:

https://dax.do/PhdF94EIuD9ygh

Sales YTD Previous Month is the measure similar to the one you're interested in.

Sales YTD Current Month is a traditional YTD measure.

When the maximum Date filtered is in October 2007

• Sales YTD Previous Month returns YTD Sales up to 30 Sep 2007 (1 Jun 2007 - 30 Sep 2007).
• Sales YTD Current Month returns YTD Sales up to 31 Oct 2007 (1 Jun 2007 - 31 Oct 2007).

Is that what you were looking for?

Regards