Forum Discussion
Anonymous
6 years agoNot applicable
Difference between two values by Week
Hey guys, I hope all is well! I'm looking for a DAX command that can produce the below results. I have a series of weeks (not dates) that need to show a count up or down and ideally a per...
- Anonymous6 years ago
Hi!
This here might work, using a similar function as the Lag function
https://community.powerbi.com/t5/Desktop/Compute-Lead-and-Lag/td-p/425809All the best,
Pétur
Anonymous
6 years agoNot applicable
// That's pretty easy in fact if you have the
// right structure. The table that stores
// weeks must have a column that is a contiguous
// sequence of integers. Let's name the column
// WeekID. Normally, such a column would be hidden
// as it's just a key for the weeks to make them
// distinguishable from each other. This table
// will be connected to some fact table with
// measures.
[Week-by-Week Delta] =
IF(
// When we calculate the delta,
// we have to make sure that
// only one week is visible in
// the current context.
HASONEVALUE( Weeks[WeekID] ),
var __currentWeekId = SELECTEDVALUE( Weeks[WeekID] )
var __currentValue = [CountResult]
var __priorValue =
CALCULATE(
// This is the base measure
// of which you want to calculate
// the delta.
[CountResult],
Weeks[WeekID] = __currentWeek - 1
)
var __delta = __currentValue - __priorValue
return
__delta
)
[Increase/Decrease] = DIVIDE( [Week-by-Week Delta], [CountResult] )
You might need to adjust the measure to deal with boundary conditions. Simply test it and fix the places where it goes astray.
Best
D