cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Vsna Frequent Visitor
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.

KeyDateDemandInventory ValueHas 2 weeks of supply?
A1Monday, July 29, 20191050If(50>10+20,1,0) ->1
A2Monday, July 29, 20192020If(20>20+20,1,0) -> 0
A3Monday, July 29, 2019520If(20>5+10,1,0) ->1
A1Monday, August 5, 20192030If(30>20+30,1,0) ->0
A2Monday, August 5, 20192040If(40>20+5,1,0) ->1
A3Monday, August 5, 2019105If(5>10+10,1,0) ->0
A1Monday, August 12, 20193030If(30>30+10,1,0) ->0
A2Monday, August 12, 2019540If(40>5+10,1,0) ->1
A3Monday, August 12, 20191050If(50>10+60,1,0) ->0
A1Monday, August 19, 20191050If(50>10+30,1,0) ->1
A2Monday, August 19, 20191030If(30>10+40,1,0) ->0
A3Monday, August 19, 20196050If(50>60+10,1,0) ->0
A1Monday, August 26, 20193040 
A2Monday, August 26, 20194030 
A3Monday, August 26, 20191010 
     

 

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
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)

View solution in original post

8 REPLIES 8
Super User
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:

snipa.PNG

Vsna Frequent Visitor
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.

KeyDateDemandInventory Value
A1Monday, July 29, 201910100
A2Monday, July 29, 201920100
A3Monday, July 29, 201951
A1Monday, August 5, 20192040
A2Monday, August 5, 20192030
A3Monday, August 5, 20191020
A1Monday, August 12, 20193030
A2Monday, August 12, 2019550
A3Monday, August 12, 20191050
A1Monday, August 19, 20191070
A2Monday, August 19, 20191020
A3Monday, August 19, 201960100
A1Monday, August 26, 20193020
A2Monday, August 26, 20194060
A3Monday, August 26, 2019102
    

 

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
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
Frequent Visitor

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

@Cmcmahan :

 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
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
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
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)

View solution in original post

Vsna Frequent Visitor
Frequent Visitor

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

Perfect, thanks a lot @Cmcmahan 

Helpful resources

Announcements
New Topics Started Badges Coming

New Topics Started Badges Coming

We're releasing new versions of the badge that everyone's talking about. ;) Check your inbox for notifications.

MBAS 2020

Save the new date (and location)!

Our business applications community is growing—so we needed a different venue, resulting in a new date and location. See you there!

Difinity Conference

Difinity Conference

The largest Power BI, Power Platform, and Data conference in New Zealand

Top Solution Authors
Top Kudoed Authors (Last 30 Days)