cancel
Showing results for
Did you mean:
Vsna Frequent Visitor

## logic - if condition based on sum of records of two weeks

demand -> demand for that week per product

Inventory Value is inventory for that product for that week.

Key-> is the unique identifier for a product.

Has 2 weeks of supply -> if w1 inventory value of a product is greater than or equal to (this week’s demand for that product + next week’s demand for that product, then yes otherwise no).

% of product that has 2 weeks of supply -> number of yes in has 2 weeks of supply / total number of products , per week.

Final result is % of products that has 2weeks of supply column in 2nd table.

 Key Date Demand Inventory Value Has 2 weeks of supply? A1 Monday, July 29, 2019 10 50 If(50>10+20,1,0) ->1 A2 Monday, July 29, 2019 20 20 If(20>20+20,1,0) -> 0 A3 Monday, July 29, 2019 5 20 If(20>5+10,1,0) ->1 A1 Monday, August 5, 2019 20 30 If(30>20+30,1,0) ->0 A2 Monday, August 5, 2019 20 40 If(40>20+5,1,0) ->1 A3 Monday, August 5, 2019 10 5 If(5>10+10,1,0) ->0 A1 Monday, August 12, 2019 30 30 If(30>30+10,1,0) ->0 A2 Monday, August 12, 2019 5 40 If(40>5+10,1,0) ->1 A3 Monday, August 12, 2019 10 50 If(50>10+60,1,0) ->0 A1 Monday, August 19, 2019 10 50 If(50>10+30,1,0) ->1 A2 Monday, August 19, 2019 10 30 If(30>10+40,1,0) ->0 A3 Monday, August 19, 2019 60 50 If(50>60+10,1,0) ->0 A1 Monday, August 26, 2019 30 40 A2 Monday, August 26, 2019 40 30 A3 Monday, August 26, 2019 10 10

This should be our final table to be used in a visual:

 Date % of products that has 2weeks of supply 29th July (1+0+1)/3 5th August (0+1+0)/3 12th  August (0+1+0)/3 19th August (1+0+0)/3
1 ACCEPTED SOLUTION

Accepted Solutions Super User

## Re: logic - if condition based on sum of records of two weeks

Ah, I just realized my mistake.  I didn't see that you had to sum this week's demand with next week's demand in order to get your boolean value.  The measure gets a bit crazier since we need to determine the [Demand] from the next week for each value of Key:

```%Products with 2wk Supply =
VAR nextWeek = SELECTEDVALUE('Table'[Date])+7
RETURN
IF(nextWeek IN ALL('Table'[Date]), //Handle the case with no Demand entries for next week
DIVIDE(
SUMX('Table',
IF(('Table'[Demand] +
CALCULATE(SELECTEDVALUE('Table'[Demand]), FILTER(ALLEXCEPT('Table', 'Table'[Key]), 'Table'[Date] = nextWeek)))            <'Table'[Inventory],
1)
),
COUNT('Table'[Key])
)+0)```
8 REPLIES 8 Super User

## Re: logic - if condition based on sum of records of two weeks

If all you're after is your second table, and you don't care about storing the intermediate results, you should be able to use this measure in a visual that splits data by date:

```% of products with 2week supply =
DIVIDE( CALCULATE(COUNTROWS('Table1'), FILTER('Table1', 'Table1'[Demand]<='Table1'[Inventory Value]/2)),         COUNTROWS('Table1'))```

You can also use a similar COUNT measure with the same filter, but instead of dividing it by the total, in the values bucket, choose to show it as a % of grand total or row/column total if you're using a matrix: Vsna Frequent Visitor

## Re: logic - if condition based on sum of records of two weeks

@Cmcmahan : Thanks for quick reply. However it is not giving me correct answers in all cases.

 Key Date Demand Inventory Value A1 Monday, July 29, 2019 10 100 A2 Monday, July 29, 2019 20 100 A3 Monday, July 29, 2019 5 1 A1 Monday, August 5, 2019 20 40 A2 Monday, August 5, 2019 20 30 A3 Monday, August 5, 2019 10 20 A1 Monday, August 12, 2019 30 30 A2 Monday, August 12, 2019 5 50 A3 Monday, August 12, 2019 10 50 A1 Monday, August 19, 2019 10 70 A2 Monday, August 19, 2019 10 20 A3 Monday, August 19, 2019 60 100 A1 Monday, August 26, 2019 30 20 A2 Monday, August 26, 2019 40 60 A3 Monday, August 26, 2019 10 2

It gives following results:

July 29th - 0.67

August 5th - 0.67

August 12th - 0.67

August 19th - 0.67

this is because, inventory value is not divided equally among two weeks. Super User

## Re: logic - if condition based on sum of records of two weeks

How are those not the correct results?

For Jul29, A1 & A2 have enough inventory for 2+ weeks.

For Aug5, A1 & A3

For Aug12, A2 & A3

For Aug19, A1 & A2.

Each of these is 2/3 or .67 or 66.7%.  If you don't like the format of the output, you can change it in the modeling ribbon.

Vsna Frequent Visitor

## Re: logic - if condition based on sum of records of two weeks

August 5th  - only A2. A1's  Inventory = 40, demand for 5th Aagust =20, demand for 12th august = 30, so 30+20>40, hence it doesn't have two weeks of supply. Super User

## Re: logic - if condition based on sum of records of two weeks

Ah, nevermind. You've posted a table with different supply values in your reply than what was in your original post.  The 66% result is correct for most values in the 2nd version.

Which version of the data is the measure being used on?  Could you put it all into a screenshot?

Vsna Frequent Visitor

## Re: logic - if condition based on sum of records of two weeks

Hi,

This is dummy data. My dataset is huge, that's why I expained the logic it step by step. Super User

## Re: logic - if condition based on sum of records of two weeks

Ah, I just realized my mistake.  I didn't see that you had to sum this week's demand with next week's demand in order to get your boolean value.  The measure gets a bit crazier since we need to determine the [Demand] from the next week for each value of Key:

```%Products with 2wk Supply =
VAR nextWeek = SELECTEDVALUE('Table'[Date])+7
RETURN
IF(nextWeek IN ALL('Table'[Date]), //Handle the case with no Demand entries for next week
DIVIDE(
SUMX('Table',
IF(('Table'[Demand] +
CALCULATE(SELECTEDVALUE('Table'[Demand]), FILTER(ALLEXCEPT('Table', 'Table'[Key]), 'Table'[Date] = nextWeek)))            <'Table'[Inventory],
1)
),
COUNT('Table'[Key])
)+0)```
Vsna Frequent Visitor

## Re: logic - if condition based on sum of records of two weeks

Perfect, thanks a lot @Cmcmahan

Announcements #### New Topics Started Badges Coming  