cancel
Showing results for
Did you mean:
Frequent Visitor

## Counting instances of measure results

Hi everyone!

I have been working on a series of measures to determine how many weeks my inventory is expected to last when paired with a weekly sales forecast.

I have a starting or current inventory quantity and an estimated forecast by week.

I created a measure to calculate the running total of the forecast quantity:

ForecastQTYRunningTotal =
CALCULATE(
SUM('ForecastTable'[Forecast Quantity]),
FILTER(
ALLSELECTED('ForecastTable'[Forecast Date]),
ISONORAFTER('ForecastTable'[Forecast Date], MAX('ForecastTable'[Forecast Date]),DESC)
)
)

then another to invert the running total:

ForecastInv = [ForecastQTYRunningTotal]*-1

and then another measure to calculate the sum of the forecast against the current inventory quantity over each week listed in the forecast (realizing I could probably combine these two into one but trying to piece this all together step by step):

InvRemaining = sum('InventoryTable'[inventory])+[ForecastInv]

I then created yet another measure to indicate whether the clauclation by the previous measure is > than 0 with:

Is_InvRemaining_+ =
var WksOnHnd = IF([InvRemaining]>0,"yes","no")
return WksOnHnd

The results of these four measures produce a table that looks like the following:

 SKU# Forecast Date Sum of Forecast Quantity ForecastQTYRunningTotal ForecastInv InvRemaining Is_InvRemaining_+ 36 1/18/2021 2494 2494 -2494 12985 yes 36 1/25/2021 2452 4946 -4946 10533 yes 36 2/1/2021 2798 7744 -7744 7735 yes 36 2/8/2021 2920 10664 -10664 4815 yes 36 2/15/2021 2364 13028 -13028 2451 yes 36 2/22/2021 2228 15256 -15256 223 yes 36 3/1/2021 2386 17642 -17642 -2163 no 36 3/8/2021 2140 19782 -19782 -4303 no 36 3/15/2021 2090 21872 -21872 -6393 no 36 3/22/2021 2120 23992 -23992 -8513 no 36 3/29/2021 2868 26860 -26860 -11381 no 36 4/5/2021 2734 29594 -29594 -14115 no

My question is how do I count the number of instances which return a "yes" to indicate how many weeks my current inventory will last? I have also tried this by returning a 1 or 0 with the intention to calculate the sum but have run into roadblocks with both the SUM function and the COUNT function as they only apply to columns (?).

My hope is to be able to populate a card that would indicate the number of "yes" or "1" instances as a total number of weeks

1 ACCEPTED SOLUTION
Super User IV

@dgwoodard , Try like

measure =

var _tab = addcolumns(summarize(Table, Table[SKU#], Table[Forecast Date), "_1",[InvRemaining])

return

countx(_tab, if([_1]>0,1,blank())

Proud to be a Super User!

5 REPLIES 5
Frequent Visitor

Also worth noting that I have a slicer filtering the data table and measures by SKU # in order to isolate the detail of each product.

Super User IV

@dgwoodard , Try a measure like

measure =

var _tab = addcolumns(summarize(Table, Table[SKU#], Table[Forecast Date), "_1",[InvRemaining])

return

countx(_tab, if(_tab[_1]>0,1,blank())

Proud to be a Super User!

Frequent Visitor

I feel like this is really close to success but getting an error of "Cannot find table '_tab'."

Super User IV

@dgwoodard , Try like

measure =

var _tab = addcolumns(summarize(Table, Table[SKU#], Table[Forecast Date), "_1",[InvRemaining])

return

countx(_tab, if([_1]>0,1,blank())

Proud to be a Super User!

Frequent Visitor

Sucess! - thank you.

Announcements