cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Regular Visitor

## 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

1 ACCEPTED SOLUTION
Regular Visitor

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 3
Super User

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.

Proud to be a Super User!

"Tell me and I’ll forget; show me and I may remember; involve me and I’ll understand."
Need Power BI consultation, get in touch with me on LinkedIn or hire me on UpWork.
Regular Visitor

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.

Regular Visitor

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

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

#### Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

#### New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

Top Solution Authors
Top Kudoed Authors