Product Calculation - Earliest Criteria

[Anonymous]

Hello Guys,

I'm having a challenge on this scenario. here's my powerbi table. (Direct Query)

I added the minute column and hour column.

So basically I have a rule for each item#.

If product is A, multiplier is 3

If product is B, multiplier is 2

If product is C, multiplier is 5

The one that i got stuck is the computation if there's two Item# or more in a minute and on hour.

The rule that I need is if there's two or more item# in a minute on an hour.

It wil calculate(multiplier) all the product on that minute with the earliest timestamp item# . If it doesnt make sense here's what it should look like

Item#	Time Stamp	Quantity	Minute	Hour	Total	Comments
A	1:43:01 PM	10	43	13	30	<- hour 13 + minute 43 Earliest timestamp for that minute is item# A, That means on Minute 43, we will use Multiplier of 3
C	1:43:05 PM	10	43	13	30	<- hour 13 + minute 43 Earliest timestamp for that minute is item# A, That means on Minute 43, we will use Multiplier of 3
B	1:48:01 PM	12	48	13	24	<-- hour 13 this is not affected because theres only 1 item# in a minute which is item# B
C	1:47:01 PM	11	47	13	55	<-- hour 13 this is not affected because theres only 1 item# in a minute which is item# C
A	2:39:03 PM	11	39	14	33
B	2:39:12 PM	10	39	14	30
C	3:59:13 PM	12	59	15	60
C	4:03:53 PM	12	3	16	60
C	4:12:29 PM	10	12	16	50
C	4:12:29 PM	10	12	16	50

Here's my PBIX

DROPBOX PBIX 

[rajulshah · Accepted solution]

Hello @Anonymous,

Please create a calculated column as below:

Product Calculation = 
VAR MinimumIndex = CALCULATE(MIN('Table'[Item#]),FILTER('Table','Table'[Hour]=EARLIER('Table'[Hour]) && 'Table'[Minute]=EARLIER('Table'[Minute])))
VAR Multiplier = IF(MinimumIndex="A",3,IF(MinimumIndex="B",2,IF(MinimumIndex="C",1)))
RETURN 'Table'[Quantity]*Multiplier 

I got the result as below:

Let me know if this is not what you want. 

[v-eachen-msft · Accepted solution]

Hi @Anonymous ,

You could create a new column as the following DAX:

Column =
VAR time =
    CALCULATE (
        MIN ( 'Table'[Time Stamp] ),
        ALLEXCEPT ( 'Table', 'Table'[Minute], 'Table'[Hour] )
    )
VAR items =
    LOOKUPVALUE ( 'Table'[Item#], 'Table'[Time Stamp], time )
VAR multiplier =
    SWITCH ( items, "A", 3, "B", 2, "C", 5 )
RETURN
    'Table'[Quantity] * multiplier

Here is the result for your reference.

[rajulshah · Accepted solution]

Hello @Anonymous,

Please create a calculated column as below:

Product Calculation = 
VAR MinimumIndex = CALCULATE(MIN('Table'[Item#]),FILTER('Table','Table'[Hour]=EARLIER('Table'[Hour]) && 'Table'[Minute]=EARLIER('Table'[Minute])))
VAR Multiplier = IF(MinimumIndex="A",3,IF(MinimumIndex="B",2,IF(MinimumIndex="C",1)))
RETURN 'Table'[Quantity]*Multiplier 

I got the result as below:

Let me know if this is not what you want.