Forum Discussion
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 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
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
3 Replies
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.
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.
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
