how to grab last instance of the prior year

[Anonymous]

Hello all,

I have a dataset with values for business days starting since 2015 util the current date.

I have a matrix visual that for the column I use year from calendar table ( linked to the dataset)

like

2017   2018  2019 2020 

for rows I used the funds list

A

B

so far everything works find now I have a matrix that the first colum list all the products and the first row is show the year date.

	2017	2018	2019
A
B

I'm using the folowing DAx to generate ceertain results

Return =

VAR _1= FIRSTNONBLANKVALUE( 'External Returns'[Date],SUM('External Returns'[Reference Growth 10K]))

VAR _2 = LASTNONBLANKVALUE( 'External Returns'[Date],SUM('External Returns'[Reference Growth 10K]))

RETURN

(_2- _1)/_1

This formual grabs the first available value in a year, for example if I'm in 2017 that value is Jan 2 ( Jan 1 is holiday , no data)

Now I need to modify this formual and for each year column grab previous yera's last avaaiable value for example for 2017 column that value would be the value for Dec 31 2016. and if there is no preivious year data avaiable for a fund the grab just the first value form the year( for exampple in 2017 column if the data set doesn't have any 2016 data then use the first no blank value for 2017)

I would appreciate any help reqriting this formula.  

[v-kelly-msft · Accepted solution]

Hi @Anonymous ,

First create a year-dimension as below:

Table 2 = VALUES('External Returns'[Year])

Then create a measure as below:

Measure = 
var _maxdate=CALCULATE(MAX('External Returns'[Date]),FILTER(ALL('External Returns'),'External Returns'[Year]=MAX('Table 2'[Year])-1&&'External Returns'[Category]=MAX('External Returns'[Category])))
var _value=CALCULATE(SUM('External Returns'[Reference Growth 10K]),FILTER(ALL('External Returns'),'External Returns'[Date]=_maxdate))
VAR _firstdate= FIRSTNONBLANKVALUE( 'External Returns'[Date],SUM('External Returns'[Reference Growth 10K]))
Return
IF(_value=BLANK(),_firstdate,_value)

And you will see:

 For the related .pbix file,pls see attached.

Best Regards,
Kelly

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

[Greg_Deckler]

@Anonymous - Having a little trouble following this. Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

The most important parts are:
1. Sample data as text, use the table tool in the editing bar
2. Expected output from sample data
3. Explanation in words of how to get from 1. to 2.

[amitchandak]

@Anonymous , you data and formula is not matching

Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

[Anonymous]

Some dummy data

Date	10K Growth
11/2/2016	10010
.. one record per business day
12/31/2016	10102
1/2/2017	10098
.... one record per business day
12/31/2017	10120

Return =

VAR _1=  FIRSTNONBLANKVALUE( 'ABC'[Date],SUM('ABC'[10K Growth]))

VAR _2 = LASTNONBLANKVALUE( 'ABC'[Date],SUM('ABC'[10K Growth]))

RETURN

(_2- _1)/_1

 now imagine a matrix with years 2016,2017,....  as the deader row. This formula for 2017 column will calculate

(10120-10098)/10098

but I need to calculate

(10120-10102)/10102  - take the value from the last day of previous year

[mahoneypat]

Please try this measure expression to get your desired result

Growth Lastday PY =
VAR maxvalue =
    LASTNONBLANKVALUE ( 'Calendar'[Date], MAX ( Growth[10K Growth] ) )
VAR minvalue =
    FIRSTNONBLANKVALUE ( 'Calendar'[Date], MAX ( Growth[10K Growth] ) )
VAR thisyear =
    SELECTEDVALUE ( 'Calendar'[Year] )
VAR PYmaxvalue =
    CALCULATE (
        LASTNONBLANKVALUE ( 'Calendar'[Date], MAX ( Growth[10K Growth] ) ),
        'Calendar'[Year] = thisyear - 1
    )
RETURN
    IF (
        ISBLANK ( PYmaxvalue ),
        ( maxvalue - minvalue ) / minvalue,
        ( maxvalue - PYmaxvalue ) / PYmaxvalue
    )

If this works for you, please mark it as the solution.  Kudos are appreciated too.  Please let me know if not.

Regards,

Pat

[Anonymous]

Great! thanks it worked like a charm.

[v-kelly-msft]

Hi  @Anonymous ,

Glad to hear that!😊

Could you pls mark the reply as answered to close it?Much appreciated.

Best Regards,
Kelly

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

[Anonymous]

Actually I tried to mark it as answer but I got the error below.

Authentication failed for the action you are trying to do. This failure could be due to your browser not supporting JavaScript, JavaScript not being enabled, or trying to use the action URL directly in the browser address bar instead of clicking the link on the page.

Return to my original page