DAX help

[Anonymous]

Hello,

I have a dataset with two columns. First column is daily dates and second value a dolar amount. Lets assume my data start from 5/1/2018 and end on 7/19/2020.

date               value

5/1/2018        1000

...

12/31/2018    1050

...

12/31/2019       1070

...

7/19 /2020        1060

I need a Dax fundtion to:

1) For the first avaiable year in the dataset ( it's 2018 in my exmaple) divide value of 12/31 to 1000, and for all the following year ( 2019 in my example) divide value of 12/31/2019 by 12/31/2018 and so on. For the current year divide the value for the last avaiable date (7/19/2020 in my example ) by value of last day of the last year ( 12/31/2019)

I hope my explanation is clear.

Thank you in advance 

I need a measure to 

[amitchandak]

@Anonymous , Create a date calendar join with your Rable and use

measure =
Var _1 = OPENINGBALANCEYEAR =(sum(Table[Value]),Date[Date])
var _2 = firstnonblankvalue(Table[Date],sum(Table[Value]))

Var _3 =CLOSINGBALANCEYEAR(sum(Table[Value]),Date[Date])
var _4 = lastnonblankvalue(Table[Date],sum(Table[Value]))
return
divide(if(isblank(_3),_4,_3),if(isblank(_1),_2,_1))

Please note that in case of opening and closing balance I used Date from date table. While in case last and first non blank value I used Date from the table

To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :
https://radacad.com/creating-calendar-table-in-power-bi-using-dax-functions
https://www.archerpoint.com/blog/Posts/creating-date-table-power-bi
https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/

See if my webinar on Time Intelligence can help: https://community.powerbi.com/t5/Webinars-and-Video-Gallery/PowerBI-Time-Intelligence-Calendar-WTD-YTD-LYTD-Week-Over-Week/m-p/1051626#M184


Appreciate your Kudos.

[Anonymous]

Hi Amit,

Thank you! This was an interesting way to solve this. I used your idea and I end up using just firstnonblanck() and lastnonblank() functions to get the values that I need. I want to use this DAX measure in a matrix which already has year clumn header (..., 2017,2018,2019,2020) . The year column headers are dynamic meaning based on my slicer selection it might strat from 2017 or 2019 or any year that we have data for that specififc item in the slicer, but we always have the current year ( 2020). 

I just have one more question. What I actually need to do is devide lastnonblank() value of each year by lastnonblank() value of previous year, so if i'm in 2018 I need to calulculate  value for (12/31/2018) value for (12/31/2017), except for the very first year because there is no data for the prior year.

sorry for the long explanation but if I want to sumerize my question is what is the formula

to get lastnonblank value of the previous year?