Skip to main content
cancel
Showing results for 
Search instead 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

Reply
Thomasshepherd2
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

 

Thomasshepherd2_0-1714624005273.png

 

 

Thomasshepherd2_1-1714624005275.png

 

1 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

 

View solution in original post

3 REPLIES 3
danextian
Super User
Super User

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.










Did I answer your question? Mark my post as a solution!


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.
Learn with me on YouTube @DAXJutsu or follow my page on Facebook @DAXJutsuPBI.

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

 

Helpful resources

Announcements
Europe Fabric Conference

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 Carousel June 2024

Power BI Monthly Update - June 2024

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

PBI_Carousel_NL_June

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.

Top Solution Authors