Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

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

 

ItemWeek ValueBOMMonth
G2513118000BOMAug
G2513220BOMAug
G2513320BOMAug
G2513420BOMAug
G2513520BOMAug
G2513614000BOMSep
G2513730BOMSep
G2513830BOMSep
G2513930BOMSep
G2514016000BOMOct
G2514115BOMOct
G2514215BOMOct
G2514315BOMOct
M8903117000NON BOMAug
M890320NON BOMAug
M890330NON BOMAug
M890340NON BOMAug
M890350NON BOMAug
M8903612500NON BOMSep
M890370NON BOMSep
M890380NON BOMSep
M890390NON BOMSep
M8904018500NON BOMOct
M890410NON BOMOct
M890420NON BOMOct
M890430NON BOMOct

 

The formula should return the result below,

 

ItemWeekValueBOMMonth
G251313600BOMAug
G251323620BOMAug
G251333620BOMAug
G251343620BOMAug
G251353620BOMAug
G251363500BOMSep
G251373530BOMSep
G251383530BOMSep
G251393530BOMSep
G251404000BOMOct
G251414015BOMOct
G251424015BOMOct
G251434015BOMOct
M8903117000NON BOMAug
M890320NON BOMAug
M890330NON BOMAug
M890340NON BOMAug
M890350NON BOMAug
M8903612500NON BOMSep
M890370NON BOMSep
M890380NON BOMSep
M890390NON BOMSep
M8904018500NON BOMOct
M890410NON BOMOct
M890420NON BOMOct
M890430NON BOMOct

 

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

 

Thank you!

Need Help
  • PaulDBrown's avatar
    PaulDBrown
    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
    _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

2 Replies

  • PaulDBrown's avatar
    PaulDBrown
    Icon for Community Champion rankCommunity Champion
    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
    _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's avatar
      Anonymous
      Not applicable
      4 years ago

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