Add values in a vertical way per product and determine week coverage

[rod2k21]

Hi experts!!

I need your assistance with the following please:

As you can see, I have a single table with info on the products. Requirement: I need to create a measure or something to know how many weeks my inventory ("UnidadesInventario") can cover (considering consumption by week "Consumo Sem").

For example, Coca is covered for two weeks because we have 5 available units and we are consuming 1 in the first week and 4 in the second week, so the result for Coca with this data...would be: 2.

I've tried SUMX function, however, I think I need something else because I have to control the number of weeks (or rows) and add some conditional sentences...

Any ideas, tips, or even code to share?

Thanks in advance.

[Anonymous · Accepted solution]

Hi @rod2k21 ,

I have created a simple sapmle, please refer to it to see if it helps you.

Create measures.

Measure =
VAR _1 =
    CALCULATE (
        MAX ( 'Table'[UnidadesInventario] ),
        FILTER (
            ALL ( 'Table' ),
            'Table'[insumos] = SELECTEDVALUE ( 'Table'[insumos] )
        )
    )
VAR _sum =
    CALCULATE (
        SUM ( 'Table'[consumo Sem] ),
        FILTER (
            ALL ( 'Table' ),
            'Table'[Sem] <= SELECTEDVALUE ( 'Table'[Sem] )
                && 'Table'[insumos] = SELECTEDVALUE ( 'Table'[insumos] )
        )
    )
RETURN
    IF ( _sum <= _1, 1, 0 )

result = COUNTAX(filter(ALL('Table'),'Table'[insumos]=SELECTEDVALUE('Table'[insumos])&&[Measure]=1),[Measure])

Best Regards

Community Support Team _ Polly

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[rod2k19]

Great, this is exactly what I needed! Thanks @Anonymous.

[Greg_Deckler]

@rod2k21 Not sure exactly what you want but it sounds like this: Days of Supply - Microsoft Power BI Community

[rod2k19]

Thanks @Greg_Deckler , great reference, it helps!