Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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).
Solved! Go to 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
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
42 | |
21 | |
21 | |
14 | |
13 |
User | Count |
---|---|
43 | |
36 | |
33 | |
18 | |
18 |