DAX: Normalizing/Spreading out values over a month if condition is met

[Anonymous]

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 conditions are met. In the table below, if the Item is a BOM (e.g. Item G251), the value in the first week of the month (e.g. Aug Week 31) should be spreaded across the remaining weeks of the month and be added to the value of the particular week (e.g. Aug Week 31, 32, 33, 34, 35). Therefore for Item G251 the value of 18000 will be divided and spreaded across Weeks 31-35. This will be so for 14000 in Week 36 September that will spread across Week 36-39 and 16000 in Week 40 that will spread across Week 40-43.

Item M890 will not have its value normalized/spreaded out as it is a NON BOM item.

Item	Week	Value	BOM	Month
G251	31	18000	BOM	Aug
G251	32	20	BOM	Aug
G251	33	20	BOM	Aug
G251	34	20	BOM	Aug
G251	35	20	BOM	Aug
G251	36	14000	BOM	Sep
G251	37	30	BOM	Sep
G251	38	30	BOM	Sep
G251	39	30	BOM	Sep
G251	40	16000	BOM	Oct
G251	41	15	BOM	Oct
G251	42	15	BOM	Oct
G251	43	15	BOM	Oct
M890	31	17000	NON BOM	Aug
M890	32	0	NON BOM	Aug
M890	33	0	NON BOM	Aug
M890	34	0	NON BOM	Aug
M890	35	0	NON BOM	Aug
M890	36	12500	NON BOM	Sep
M890	37	0	NON BOM	Sep
M890	38	0	NON BOM	Sep
M890	39	0	NON BOM	Sep
M890	40	18500	NON BOM	Oct
M890	41	0	NON BOM	Oct
M890	42	0	NON BOM	Oct
M890	43	0	NON BOM	Oct

The formula should return the result below,

Item	Week	Value	BOM	Month
G251	31	3600	BOM	Aug
G251	32	3620	BOM	Aug
G251	33	3620	BOM	Aug
G251	34	3620	BOM	Aug
G251	35	3620	BOM	Aug
G251	36	3500	BOM	Sep
G251	37	3530	BOM	Sep
G251	38	3530	BOM	Sep
G251	39	3530	BOM	Sep
G251	40	4000	BOM	Oct
G251	41	4015	BOM	Oct
G251	42	4015	BOM	Oct
G251	43	4015	BOM	Oct
M890	31	17000	NON BOM	Aug
M890	32	0	NON BOM	Aug
M890	33	0	NON BOM	Aug
M890	34	0	NON BOM	Aug
M890	35	0	NON BOM	Aug
M890	36	12500	NON BOM	Sep
M890	37	0	NON BOM	Sep
M890	38	0	NON BOM	Sep
M890	39	0	NON BOM	Sep
M890	40	18500	NON BOM	Oct
M890	41	0	NON BOM	Oct
M890	42	0	NON BOM	Oct
M890	43	0	NON BOM	Oct

I'm open to measures/new columns that return the above desired result. Would greatly appreciate any inputs on this.

Thank you!

[PaulDBrown · Accepted solution]

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
    _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

[Anonymous]

Thank you so much @PaulDBrown , works like a charm!!