topic Re: Add values in a vertical way per product and determine week coverage in DAX Commands and Tips

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

rod2k21 — Thu, 10 Nov 2022 17:09:15 GMT

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.

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

Greg_Deckler — Thu, 10 Nov 2022 17:32:44 GMT

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

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

Anonymous — Fri, 11 Nov 2022 06:13:19 GMT

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.

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

rod2k19 — Fri, 11 Nov 2022 15:17:19 GMT

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

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

rod2k19 — Fri, 11 Nov 2022 15:18:07 GMT

Thanks Greg_Deckler , great reference, it helps!