Using selectedvalue to retrieve a date returns 1899 date

[Thomasshepherd2]

Hi there

I want to have a column in a graph that calculates the profit in a graph for the year before the selected date.

Out Fin year is a bit wonky so that is why there are 11 and 12’s

This is the measure I have :

Last Year MTD Profit =

VAR SelectedDate = SELECTEDVALUE('Calendar'[Date])

VAR SelectedYear = YEAR(SelectedDate)

VAR SelectedMonth = MONTH(SelectedDate)

VAR SelectedDay = DAY(SelectedDate)

VAR StartDate =

    IF(

        MONTH(SelectedMonth) IN {11, 12},

        DATE(SelectedYear - 1, 11, 01),

        DATE(SelectedYear - 2, 11, 01)

    )

VAR EndDate =

    IF(

        MONTH(SelectedMonth) IN {11, 12},

        DATE(SelectedYear, 10, 31),

        DATE(SelectedYear - 1, 10, 31)

    )

VAR _measure2 =

    CALCULATE(

        SUM('Fact Finance Combined'[Redacted]),

        DATESBETWEEN('Fact Finance Combined'[Redacted], StartDate, EndDate)

    )

RETURN

    StartDate

    --EndDate

    --_measure2

When I put in a card to test the start and end dates it shows be the below

[danextian]

Hi @Thomasshepherd2 ,

By default, SELECTEDVALUE returns blank if there are more than one values returned. On or before/on or after feature of the slicer returns mutliple values.  Use either LASTDATE, FIRSTDATE, MIN or MAX.

[Thomasshepherd2]

Hey Thanks for the reply,

when i try to use min max first last it says it needs a column but the calendar[date] is a column.

i need to use the slicer to get the date as people will adjust the date which needs to then filter the matrix.

appreciate any additional assistance - i have looked through forumns and reddit and not finding much.

[Thomasshepherd2 · Accepted solution]

Fixed it by using LastDate in the selected year VAR

Last Year MTD Profit =

VAR SelectedDate = LASTDATE(ALLSELECTED('Calendar'[Date]))

VAR SelectedYear = YEAR(SelectedDate)-1

VAR SelectedMonth = MONTH(SelectedDate)

VAR SelectedDay = DAY(SelectedDate)

VAR StartDate =

    IF(

        MONTH(SelectedMonth) IN {11, 12},

        DATE(SelectedYear - 1, 11, 01),

        DATE(SelectedYear - 2, 11, 01)

    )

VAR EndDate =

    IF(

        MONTH(SelectedMonth) IN {11, 12},

        DATE(SelectedYear, 10, 31),

        DATE(SelectedYear - 1, SelectedMonth, SelectedDay)

    )

VAR _measure2 =

    CALCULATE(

        SUM('Fact Finance Combined'[Redacted]),

        DATESBETWEEN('Fact Finance Combined'[Redacted], StartDate, EndDate)

    )

RETURN

    --StartDate

    --EndDate

    _measure2