Forum Discussion

Thomasshepherd2's avatar
Thomasshepherd2
Frequent Visitor
2 years ago
Solved

Using selectedvalue to retrieve a date returns 1899 date

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 th...
Need Help
  • Thomasshepherd2's avatar
    Thomasshepherd2
    2 years ago

    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

     

Thomasshepherd2's avatar
Thomasshepherd2
Frequent Visitor
2 years ago

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's avatar
Thomasshepherd2
Frequent Visitor
2 years ago

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