Forum Discussion
Anonymous
4 years agoNot applicable
DAX: Normalizing/Spreading out values over a month if condition is met
Hello there, I have a table that consists of the columns below. I'm trying to create a DAX code that normalizes/spreads out values in a column (Value) over the course of the month if certain cond...
- 4 years ago
See if this works... I've created step by step measures so it's easier to see what the calculations deliver, though you could probably fuse it all into a single measure using variables.
Weeks by Month = CALCULATE ( COUNT ( 'Table'[Week ] ), ALLEXCEPT ( 'Table', 'Table'[Month], 'Table'[Item] ) )First week value = VAR _MinWeek = CALCULATE ( MIN ( 'Table'[Week ] ), ALLEXCEPT ( 'Table', 'Table'[Month], 'Table'[Item] ) ) VAR _Val = CALCULATE ( SUM ( 'Table'[Value] ), FILTER ( ALLEXCEPT ( 'Table', 'Table'[Month], 'Table'[Item] ), 'Table'[Week ] = _MinWeek ) ) RETURN _ValSpread = DIVIDE([First week value], [Weeks by Month])Final measure = VAR _BOM = IF ( SUM ( 'Table'[Value] ) = [First week value], [Spread], [Spread] + SUM ( 'Table'[Value] ) ) RETURN IF ( MAX ( 'Table'[BOM] ) = "BOM", _BOM, SUM ( 'Table'[Value] ) )If you prefer a calculated column (which is probably more useful in this instance), use:
Normalized = VAR _weeks = CALCULATE ( COUNT ( 'Table'[Week ] ), FILTER ( 'Table', 'Table'[Month] = EARLIER ( 'Table'[Month] ) && 'Table'[Item] = EARLIER ( 'Table'[Item] ) ) ) VAR _MinWeek = CALCULATE ( MIN ( 'Table'[Week ] ), FILTER ( 'Table', 'Table'[Month] = EARLIER ( 'Table'[Month] ) && 'Table'[Item] = EARLIER ( 'Table'[Item] ) ) ) VAR _Val = CALCULATE ( SUM ( 'Table'[Value] ), FILTER ( 'Table', 'Table'[Month] = EARLIER ( 'Table'[Month] ) && 'Table'[Item] = EARLIER ( 'Table'[Item] ) && 'Table'[Week ] = _MinWeek ) ) VAR _Spread = DIVIDE ( _Val, _weeks ) VAR _BOM = IF ( 'Table'[Value] = _val, _Spread, _spread + 'Table'[Value] ) VAR _FINAL = IF ( 'Table'[BOM] = "BOM", _BOM, 'Table'[Value] ) RETURN _FINALI've attached the sample PBIX file
PaulDBrown
Community Champion
4 years agoSee if this works... I've created step by step measures so it's easier to see what the calculations deliver, though you could probably fuse it all into a single measure using variables.
Weeks by Month =
CALCULATE (
COUNT ( 'Table'[Week ] ),
ALLEXCEPT ( 'Table', 'Table'[Month], 'Table'[Item] )
)
First week value =
VAR _MinWeek =
CALCULATE (
MIN ( 'Table'[Week ] ),
ALLEXCEPT ( 'Table', 'Table'[Month], 'Table'[Item] )
)
VAR _Val =
CALCULATE (
SUM ( 'Table'[Value] ),
FILTER (
ALLEXCEPT ( 'Table', 'Table'[Month], 'Table'[Item] ),
'Table'[Week ] = _MinWeek
)
)
RETURN
_Val
Spread =
DIVIDE([First week value], [Weeks by Month])Final measure =
VAR _BOM =
IF (
SUM ( 'Table'[Value] ) = [First week value],
[Spread],
[Spread] + SUM ( 'Table'[Value] )
)
RETURN
IF ( MAX ( 'Table'[BOM] ) = "BOM", _BOM, SUM ( 'Table'[Value] ) )
If you prefer a calculated column (which is probably more useful in this instance), use:
Normalized =
VAR _weeks =
CALCULATE (
COUNT ( 'Table'[Week ] ),
FILTER (
'Table',
'Table'[Month] = EARLIER ( 'Table'[Month] )
&& 'Table'[Item] = EARLIER ( 'Table'[Item] )
)
)
VAR _MinWeek =
CALCULATE (
MIN ( 'Table'[Week ] ),
FILTER (
'Table',
'Table'[Month] = EARLIER ( 'Table'[Month] )
&& 'Table'[Item] = EARLIER ( 'Table'[Item] )
)
)
VAR _Val =
CALCULATE (
SUM ( 'Table'[Value] ),
FILTER (
'Table',
'Table'[Month] = EARLIER ( 'Table'[Month] )
&& 'Table'[Item] = EARLIER ( 'Table'[Item] )
&& 'Table'[Week ] = _MinWeek
)
)
VAR _Spread =
DIVIDE ( _Val, _weeks )
VAR _BOM =
IF ( 'Table'[Value] = _val, _Spread, _spread + 'Table'[Value] )
VAR _FINAL =
IF ( 'Table'[BOM] = "BOM", _BOM, 'Table'[Value] )
RETURN
_FINAL
I've attached the sample PBIX file
- Anonymous4 years agoNot applicable
Thank you so much PaulDBrown , works like a charm!!