Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

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

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
Cmcmahan
Resident Rockstar
Resident Rockstar

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

Anonymous
Not applicable

@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.

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.

 

Anonymous
Not applicable

@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.

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?

Anonymous
Not applicable

Hi,

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

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)
Anonymous
Not applicable

Perfect, thanks a lot @Cmcmahan 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.