Forum Discussion

aroucadaniel's avatar
aroucadaniel
Frequent Visitor
3 years ago
Solved

Return penultimate date from a table

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 ...
  • daXtreme's avatar
    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