Forum Discussion
Return penultimate date from a table
- 3 years ago
First, your calendar should indeed have ALL THE DAYS, not only working days. This is needed for the time-intel functions to work correctly (please, for instance consult this page). Second, it's easy to find the previous working day for a given day if you have a proper calendar and there's a column in there that informs you whether or not a day is working/non-working.
Here's a measure that gets you such a day with ease:
[Prev Working Day] = // If multiple days are visible, the base // for the calculation will be the very // first day in the context. If only one // day is visible, this very day will be // the point of orientation. You can adjust // this logic to your liking. Please don't // forget to MARK your Dates as a Date Table // in the model so that it works as expected // with the time-intel functions. var FirstDayVisible = MIN( Dates[Date] ) var PrevWorkingDay = calculate( selectedvalue( Dates[Date] ), Dates[Date] < FirstVisibleDay, // Day Type should be a column with // 2 distinct values in it: working, // non-working. Please note that // DAX is case-insensitive. Dates[Day Type] = "working", // This is technically unnecessary // IF your Dates are marked as // a Date Table. If not, you have // to keep it. removefilters( Dates ) ) return PrevWorkingDay
First, your calendar should indeed have ALL THE DAYS, not only working days. This is needed for the time-intel functions to work correctly (please, for instance consult this page). Second, it's easy to find the previous working day for a given day if you have a proper calendar and there's a column in there that informs you whether or not a day is working/non-working.
Here's a measure that gets you such a day with ease:
[Prev Working Day] =
// If multiple days are visible, the base
// for the calculation will be the very
// first day in the context. If only one
// day is visible, this very day will be
// the point of orientation. You can adjust
// this logic to your liking. Please don't
// forget to MARK your Dates as a Date Table
// in the model so that it works as expected
// with the time-intel functions.
var FirstDayVisible = MIN( Dates[Date] )
var PrevWorkingDay =
calculate(
selectedvalue( Dates[Date] ),
Dates[Date] < FirstVisibleDay,
// Day Type should be a column with
// 2 distinct values in it: working,
// non-working. Please note that
// DAX is case-insensitive.
Dates[Day Type] = "working",
// This is technically unnecessary
// IF your Dates are marked as
// a Date Table. If not, you have
// to keep it.
removefilters( Dates )
)
return
PrevWorkingDay