Return penultimate date from a table

[aroucadaniel]

Hello!

I need to calculate the daily change in the value of a column. My base has a Calendar Dimension, and the idea is to create a measure that makes the variation based on the previous working day.

I've already tried to use the DAX function "PROVIOUSDAY" and the "DATE ​​ADD", but this creates a problem because it literally searches for a previous day and not the previous date available in the Calendar Dimension (which only has working days).

[daXtreme · Accepted solution]

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