I watched all the classes on the website and searched for the whole internet, but I cannot get this to work
“Previous Value” is a column created by code:
Previous Value =
Var PreviousRowIndex = CALCULATE(
MAX(TradingData_10[Index]),
FILTER(TradingData_10,TradingData_10[Index]<EARLIER(TradingData_10[Index])))
Return
CALCULATE(
MAX(TradingData_10[Closing price]),
FILTER(TradingData_10, TradingData_10[Index]=PreviousRowIndex))
How do I adjust the code so if I selected a specific Company name, the column still shows correct data; ( There are around 10 company data in the dataset)
Is there a way to do the previous calculation using a Dax measure, not a new column.
eDNA Expert @Greg has created a phenomenal DAX pattern section that directly addresses both of your questions::
Re: doing this as a measure – @Greg works through the preferred way to do this, using variables to capture the current row context, rather than EARLIER.
Re: getting it to work after filtering on Company - he works through this as well, in terms of how to apply the pattern with ALLSELECTED, rather than ALL.
If you still have any questions after working through his material, please just give a shout.
Thank you! Because of you, I noticed and started to like my username
About the problem:
I analyzed the article and wrote the formula I needed. Sadly, I still have one more question; is there a way to automatically reference the last cell that contains a value? How do I make a formula that ignores the blanks?
For example, please see the below formula; it references the last cell with a value excluding weekends. I want to make this formula automatic, so, for instance, if weekends suddenly are four days instead of two, the procedure would not need to be adjusted.
Turnover Previous Date =
VAR PreviousMeasure = CALCULATE([Turnover 2],DATEADD(Calendar_Perfect[Date],-1,DAY))
OK, When we start getting into the realm of discontinuous dates, as @Greg indicates, the DATEADD -1 construct is no longer going to work. Thus, we are going to have to switch over to the approach of using a variable to capture the current row context, which is fine.
I’m also going to assume that you are using @Melissa’s awesome extended date table. If not, you’ll see in a minute how much easier it will make this solution.
First, in terms of excluding blanks, I think the best way to do this will be via the filter pane. You can also do this in DAX, but as this post demonstrates, doing it via the filter pane typically is much, much easier both to implement and for someone else looking at your model to understand.
Now once we’ve got the extended date table set up and the blanks filtered out, the next step is getting the previous date ignoring weekends. I worked through that question in the post below, and @Sam.McKay also addressed it in a follow-up video:
So,the final step per your question is addressing extended weekends. If this four day weekend that you mention is just an isolated case, it can be addressed in the holiday table. If, however, you live in a wonderful world in which all weekends suddenly become four days, then this will be addressed in the extended date table in terms of how you define the IsWorking Day field in the extended date table code.
Your revised version of the measure discussed above will now look like this, and be completely dynamic as you adjust your weekend definition:
Previous Value No Weekends No Holidays =
VAR SelDate = SELECTEDVALUE( Dates[Date] )
VAR PrevDate =
CALCULATE(
MAX( Dates[Date] ),
FILTER(
ALL( Dates ),
Dates[Date] < SelDate &&
Dates[IsWorkingDay] = TRUE &&
Dates[IsHoliday] = FALSE
)
)
RETURN
PrevDate
I think that should get you where you want to go. If not, please post a PBIX with some sample data and we can work through the specifics of you particular use case.
I hope this is helpful.
Brian
P.S. In the extended date table, there is another field call IsBusiness Day, which is equivalent to
