Forum Discussion

scorbin-j's avatar
scorbin-j
Helper I
1 year ago
Solved

Conditional formatting based on quantity, lead time, and date

I have a date table and a table that shows inventory transactions over time from where I have created a burndown measure for a rolling total: 

 

 

BurndownAnalysis = 
VAR LastConvertedQty = 
    CALCULATE(
        LASTNONBLANK(
            Burndown[quantity], 
            CALCULATE(MAX(date[Date]))
        )
    )
RETURN
    IF(
        ISBLANK(
            CALCULATE(
                SUM(Burndown[quantity]),
                FILTER(
                    ALLSELECTED(date[Date]]),
                    ISONORAFTER(
                        date[Date], 
                        MAX(date[Date]), 
                        DESC
                    )
                )
            )
        ),
        LastConvertedQty,
        CALCULATE(
            SUM(Burndown[quantity]),
            FILTER(
                ALLSELECTED(date[Date]),
                ISONORAFTER(
                    date[Date], 
                    MAX(date[Date]), 
                    DESC
                )
            )
        )
    )

 

 

I also have material table that shows lead time for each material. I would like to format the quantities from the burndown measure so that when the quantity goes to 0 or below it's red and then yellow for the quantities under the dates that are within the lead time of the date when the quantity goes to zero. I think this means I need to find the "zero quantity date" for each material, but I'm not sure how to do that. And then calculate the dates that are within the lead time of the "zero quantity date".

Example:


I would also note that the quantites are summed by week in my visual. 

Need Help
  • scorbin-j's avatar
    scorbin-j
    1 year ago

    Figured it out: 

    VAR LastConvertedQty = 
    CALCULATE(
        LASTNONBLANK(
            Burndown[quantity], 
            CALCULATE(MAX(date[Date]))
        )
    )
VAR _burn =
    IF(
        ISBLANK(
            CALCULATE(
                SUM(Burndown[quantity]),
                FILTER(
                    ALLSELECTED(date[Date]),
                    ISONORAFTER(
                        date[Date], 
                        MAX(date[Date]), 
                        DESC
                    )
                )
            )
        ),
        LastConvertedQty,
        CALCULATE(
            SUM(Burndown[quantity]),
            FILTER(
                ALLSELECTED(date[Date]),
                ISONORAFTER(
                    date[Date], 
                    MAX(date[Date]), 
                    DESC
                )
            )
        )
    )

VAR _material = 
    SELECTEDVALUE(MaterialMaster[Material_Master])

VAR LeadTime =
    LOOKUPVALUE(
        MaterialMaster[LeadTime], 
        MaterialMaster[Material_Master], _material
    )

VAR _zerodate =
CALCULATE (
    CALCULATE (
        MIN ( date[Date] ),
        FILTER (
            ALL ( dimTime ),
            [BurndownAnalysis] <= 0
                && date[Date] > TODAY ()
        )
    ),
    FILTER (
        ALL (MaterialMaster),
        MaterialMaster[material_master] = _material
    )
)

VAR LeadTimeWindow = _zerodate - LeadTime

RETURN 
    IF(
        _burn <= 0, 
        1, // Red
        IF(
            _burn > 0 && MAX(date[Date]) >= LeadTimeWindow && MAX(date[Date]) <= _zerodate, 
            2, // Yellow
            3  // Green
        )
    )

1 Reply

  • scorbin-j's avatar
    scorbin-j
    Helper I
    1 year ago

    Figured it out: 

    VAR LastConvertedQty = 
    CALCULATE(
        LASTNONBLANK(
            Burndown[quantity], 
            CALCULATE(MAX(date[Date]))
        )
    )
VAR _burn =
    IF(
        ISBLANK(
            CALCULATE(
                SUM(Burndown[quantity]),
                FILTER(
                    ALLSELECTED(date[Date]),
                    ISONORAFTER(
                        date[Date], 
                        MAX(date[Date]), 
                        DESC
                    )
                )
            )
        ),
        LastConvertedQty,
        CALCULATE(
            SUM(Burndown[quantity]),
            FILTER(
                ALLSELECTED(date[Date]),
                ISONORAFTER(
                    date[Date], 
                    MAX(date[Date]), 
                    DESC
                )
            )
        )
    )

VAR _material = 
    SELECTEDVALUE(MaterialMaster[Material_Master])

VAR LeadTime =
    LOOKUPVALUE(
        MaterialMaster[LeadTime], 
        MaterialMaster[Material_Master], _material
    )

VAR _zerodate =
CALCULATE (
    CALCULATE (
        MIN ( date[Date] ),
        FILTER (
            ALL ( dimTime ),
            [BurndownAnalysis] <= 0
                && date[Date] > TODAY ()
        )
    ),
    FILTER (
        ALL (MaterialMaster),
        MaterialMaster[material_master] = _material
    )
)

VAR LeadTimeWindow = _zerodate - LeadTime

RETURN 
    IF(
        _burn <= 0, 
        1, // Red
        IF(
            _burn > 0 && MAX(date[Date]) >= LeadTimeWindow && MAX(date[Date]) <= _zerodate, 
            2, // Yellow
            3  // Green
        )
    )