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 cond...
  • PaulDBrown's avatar
    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