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
Fabcon_Europe_Social_Bogo

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.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

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