Measure that dynamically calculates first value of a time series

DAX DAX Calculations
1

Hi Team,

Hoping someone can help me with something I can’t get my head around.
I’m trying to replicate that scenario with DAX to calculate percent change with a start value of a given set of data. In this case I want the start value to be dynamic based on the start date which is in turn selected via the date picker. See image below:

DAX%20getting%20first%20value
DAX getting first value.png807×602 28.9 KB

In the image, I have selected 03/08/2011 as my start date from the date picker. The value on this day is $8.88m. So what I want to do is have the measure ‘getting first value’ repeat for the whole table as indicated by the green highlight & arrow.

I thought removing all filters with ALL as indicated in the DAX measure I wrote would address this but not the case. It does seem to give the expected value only at the ‘Total’ row at the bottom.

My goal is to be able to subtract the first value from each row as a way of calculating the percent return since the first date. And with the date picker, a user should be able to dynamically view his / her return from a given start date.

What am I doing wrong?
Thanks in advance for your help & insights

Bare

2

@Bare,

You’re right – this one was trickier than it looked at first glance. Here’s a solution that works, though it took me three measures to get there. so probably not the most elegant or efficient approach:…

image
image.png581×550 29.9 KB 

Min Date Selected = 

CALCULATE(
    MINX(
        ALLSELECTED(Dates[Date] ),
        Dates[Date] 
    )
)

First Sales Selected = 

VAR MinDate = [Min Date Selected]

VAR vFirstSale =
    CALCULATE(
        [Total Sales],
        FILTER(
            Dates,
            Dates[Date] = MinDate
        )
    )

RETURN
vFirstSale


First Sales All Column = 

CALCULATE(
    MAXX(
        Dates,
        [First Sales Selected]
    ),
    ALLSELECTED( Dates[Date] )
)

I hope this is helpful. Full solution file posted below.
eDNA Forum – First Value solution.pbix (512.1 KB)

1 Like
3

Agree with you here @BrianJ, anyway thought I would share my solution also:

FirstDaySales = 
VAR FirstDay = 
    CALCULATE( MIN( Dates[Date] ),
        ALLSELECTED( Dates[Date] )
    )
VAR ThisDate = SELECTEDVALUE( Dates[Date] )
RETURN

CALCULATE( [Total Sales],
    DATEADD( Dates[Date], FirstDay - ThisDate, DAY )
)
2 Likes
4

@Melissa, @Bare,

Nice – much more efficient. This is a really clever construct:

image

Both approaches get to the finish line. Mine just does so like this.

  • Brian
1 Like
5

@BrianJ / @Melissa

Thanks a mil to you both for looking into the issue. Really awesome!

Agreed with @BrianJ both solutions get to the finish line - the objective (I never got to the finish line! :slight_smile:)

@Melissa I never imagined using DATEADD to get to the finish line. Can you please elaborate the logic behind for my learning / understanding? I’d much appreciate. The VAR ‘ThisDate’ seems to return blank, correct?

But all the same, thanks a lot to you both :slight_smile:

Bare

6

@Bare, yeah I’d be glad to

I visualized the elements used separately below, the VAR ‘ThisDate’ returns BLANK only when there is no Date context OR when more than one date is returned (like in the grand total row)
image

Here’s more on DATEADD

You can fix the grand total by switching to @BrianJ’s solution, something like:

The best of two worlds = 
SWITCH ( HASONEVALUE ( Dates[Date] ),
    TRUE(), [FirstDaySales],
    FALSE(), [First Sales Selected]
)

Here’s the updated file, I hope this was helpful.
eDNA Forum – First Value solution.pbix (543.1 KB)

1 Like
7

Hi @Melissa

This was super helpful. The DATEADD is clearer to me now - very ingenious! :slight_smile:

Many thanks & cheers,
Bare

1 Like