Supply frequency measure...

[Matty]

Hi Team,

Looking for some help to do the following...

Data as follows:

[TABLE="class: tableizer-table"]
<tbody>[TR="class: tableizer-firstrow"]
[TH]Week[/TH]
[TH]Product[/TH]
[TH]Supply[/TH]
[/TR]
[TR]
[TD]1[/TD]
[TD]Apple[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Apple[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Apple[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]Apple[/TD]
[TD]5[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]Apple[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]Apple[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]Apple[/TD]
[TD]10[/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]Apple[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD]Apple[/TD]
[TD]10[/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD]Apple[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]11[/TD]
[TD]Apple[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]12[/TD]
[TD]Apple[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]13[/TD]
[TD]Apple[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]14[/TD]
[TD]Apple[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]15[/TD]
[TD]Apple[/TD]
[TD]5[/TD]
[/TR]
[TR]
[TD]16[/TD]
[TD]Apple[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]17[/TD]
[TD]Apple[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]18[/TD]
[TD]Apple[/TD]
[TD]5[/TD]
[/TR]
[TR]
[TD]19[/TD]
[TD]Apple[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]20[/TD]
[TD]Apple[/TD]
[TD]10[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Banana[/TD]
[TD]20[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Banana[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Banana[/TD]
[TD]20[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]Banana[/TD]
[TD]20[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]Banana[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]Banana[/TD]
[TD]30[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]Banana[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]Banana[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD]Banana[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD]Banana[/TD]
[TD]20[/TD]
[/TR]
[TR]
[TD]11[/TD]
[TD]Banana[/TD]
[TD]20[/TD]
[/TR]
[TR]
[TD]12[/TD]
[TD]Banana[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]13[/TD]
[TD]Banana[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]14[/TD]
[TD]Banana[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]15[/TD]
[TD]Banana[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]16[/TD]
[TD]Banana[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]17[/TD]
[TD]Banana[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]18[/TD]
[TD]Banana[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]19[/TD]
[TD]Banana[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]20[/TD]
[TD]Banana[/TD]
[TD]0[/TD]
[/TR]
</tbody>[/TABLE]

I want a DAX measure to return the following results:

[TABLE="class: tableizer-table"]
<tbody>[TR="class: tableizer-firstrow"]
[TH]Product[/TH]
[TH]Supply Frequency[/TH]
[/TR]
[TR]
[TD]Apple[/TD]
[TD]3.20[/TD]
[/TR]
[TR]
[TD]Banana[/TD]
[TD]2.00[/TD]
[/TR]
</tbody>[/TABLE]

Which was computed using standard Excel functions as follows:

=COUNTIF(INDEX(C$2:C$41,MATCH(1,((B$2:B$41=E2)*(C$2:C$41>0)),0)+1):INDEX(C$2:C$41,MATCH(2,1/((B$2:B$41=E2)*(C$2:C$41>0)))),">=0")/COUNTIF(INDEX(C$2:C$41,MATCH(1,((B$2:B$41=E2)*(C$2:C$41>0)),0)+1):INDEX(C$2:C$41,MATCH(2,1/((B$2:B$41=E2)*(C$2:C$41>0)))),">0")

Logic used:

Per product, establish the range of interest by determining the first and last weeks where supply occurred (note that the first week is always moved on by 1 to 'correct' the range for calculation purposes). In the case of 'Apple', a count of 16 is returned (weeks 5 through to 20). Now, divided this number by the number of times supply has occurred over this same time period. For Apple, 5 is returned (supply occurred in weeks 7, 9, 15, 18 and 20). Finally, divide 16 by 5 to get the desired result: 3.2.

Hope this is clear and someone can assist.

Cheers,

Matty

 

[ranman256]

there are 20 weeks of apples and 6 supply runs. ???
weeks 4,7, 9, 15, 18 and 20

 

[Matty]

there are 20 weeks of apples and 6 supply runs. ???
weeks 4,7, 9, 15, 18 and 20

Hi,

See this bit:

Per product, establish the range of interest by determining the first and last weeks where supply occurred (note that the first week is always moved on by 1 to 'correct' the range for calculation purposes)

Hence the range of interest is weeks 5 through to 20.

Cheers,

Matty

 

[Matty]

Just bumping this in case anyone has any bright ideas...

Cheers,

Matty

 

[gazpage]

Given you haven’t had any response yet, I’ll try to get you started.

LASTNONBLANK and FIRSTNONBLANK, using the date column as the column and a CALCULATE ( SUM ( )) on the Supply column as the expression, will return the last or first date in the current context. If the your data really has 0 rather than blank, you will need to wrap it in a calculate to remove the 0s.

 

[gazpage]

Actually, it’s just a COUNTROWS divided by a MAX minus a MIN, all wrapped in a calculate <> 0

 

[Matty]

Thanks for the direction, Gaz.

Here's what I've come up with:

Supply Frequency = 
DIVIDE (
    CALCULATE ( LASTNONBLANK ( Table1[Week], [COLOR=#ff0000]0[/COLOR] ), Table1[Supply] <> 0 )
        - CALCULATE ( FIRSTNONBLANK ( Table1[Week], [COLOR=#ff0000]0[/COLOR] ), Table1[Supply] <> 0 ),
    CALCULATE ( COUNTROWS ( Table1 ), Table1[Supply] <> 0 )
        - 1
)

One thing I don't understand is what the 0 highlighted above is actually doing (though the number itself doesn't seem to influence anything - using 6 works just as well!). Can any one help?

Cheers,

Matty

 

[gazpage]

The actual number doesn't matter, just that it is a constant.

LAST/FIRSTNONBLANK look for the last of first blank returned using by the second parameter. Having a constant means that that constant is returned for any date that exists. In hindsight I think you can just replace LAST/FIRSTNONBLANK with Max / Min in this case.

I also think you can wrap the whole thing in the calculate, i.e.

Supply Frequency = 
CALCULATE (    
    DIVIDE (
        MAX ( Table1[Week] ) - MIN ( Table1[Week] ),
        COUNTROWS ( Table1 ) - 1
    ),
    Table1[Supply] <> 0
)

May be a bit easier to read in the future.

 

[Matty]

Hi Gaz,

Thanks for explaining the constant bit and providing a much neater formula.

I'm still trying to get my head around how DAX evaluates (the evaluation order seems different to how Excel formulas work). In the formula you've posted, it looks as though the Calculate part is applied first rather than last? What's your understanding of how it's working?

Cheers,

Matty

 

[gazpage]

In some ways they are like excel formulas, but CALCULATE is a notable exception.

Maybe think about CALCULATE with two parameters as:

CALCULATE (
     {calculate whatever is written here},
     {but imagine you are in a world as defined here}
)

Therefore you need to know the second parameter before you can work out what the first returns.