Forum Discussion
DAX help
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.
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?