cancel
Showing results for
Did you mean:  Helper V

## Phantom Stock Calculation (Stock >0 and Sales =0)

Hi there,
I have a basic table setup:
tblProducts (ProductID)
tblStores (StoreID)
tblData (StoreID, ProductID, Date, SOH, Sales)
tblCalendar (Date)

I am trying to count the line (ProductID & StoreID). That have More than zero SOH for the latest date of data and zero sales over 50 days.

1 ACCEPTED SOLUTION  Helper V

I was finally able to get this calculation working, by using CROSSJOIN. Other methods were just too slow.
Here is the measure, hope it is able to help someone else.

``````Phantom Stock =
CALCULATE (
SUMX (
CROSSJOIN (
VALUES ( tblProducts[ArticleCode] ),
VALUES ( tblStores[StoreCode] )
),
IF (
[Stock on Hand Latest Date] > 0
&& [Volume Sales Total (X Days)] <= 0,
[Stock on Hand Latest Date],
BLANK ()
)
)
)``````

Here are the 2 supporting measures.

``````Stock on Hand Latest Date =
VAR _MaxDate = CALCULATE(MAX(tblCalendar[Date]),ALL(tblCalendar))
RETURN
CALCULATE (
[Stock on Hand Total],
FILTER (
tblCalendar,
tblCalendar[Date] = _MaxDate
)
)``````

The Variable called _Days, is just a list of days in weeks, (1 Week = 7, 2 Weeks = 14 etc...)

``````Volume Sales Total (X Days) =
VAR _CurrentDate = CALCULATE(MAX(tblCalendar[Date]),ALL(tblCalendar))
VAR _Days = max(sptWeeks[Days])
VAR _StartDate = _CurrentDate - _Days
RETURN
CALCULATE(
[Volume Sales Total],
FILTER(
tblCalendar,
tblCalendar[Date] > _StartDate && tblCalendar[Date] <= _CurrentDate
)
)
``````  Helper V

I was finally able to get this calculation working, by using CROSSJOIN. Other methods were just too slow.
Here is the measure, hope it is able to help someone else.

``````Phantom Stock =
CALCULATE (
SUMX (
CROSSJOIN (
VALUES ( tblProducts[ArticleCode] ),
VALUES ( tblStores[StoreCode] )
),
IF (
[Stock on Hand Latest Date] > 0
&& [Volume Sales Total (X Days)] <= 0,
[Stock on Hand Latest Date],
BLANK ()
)
)
)``````

Here are the 2 supporting measures.

``````Stock on Hand Latest Date =
VAR _MaxDate = CALCULATE(MAX(tblCalendar[Date]),ALL(tblCalendar))
RETURN
CALCULATE (
[Stock on Hand Total],
FILTER (
tblCalendar,
tblCalendar[Date] = _MaxDate
)
)``````

The Variable called _Days, is just a list of days in weeks, (1 Week = 7, 2 Weeks = 14 etc...)

``````Volume Sales Total (X Days) =
VAR _CurrentDate = CALCULATE(MAX(tblCalendar[Date]),ALL(tblCalendar))
VAR _Days = max(sptWeeks[Days])
VAR _StartDate = _CurrentDate - _Days
RETURN
CALCULATE(
[Volume Sales Total],
FILTER(
tblCalendar,
tblCalendar[Date] > _StartDate && tblCalendar[Date] <= _CurrentDate
)
)
`````` Announcements #### Exclusive opportunity for Women!

Join us for a free, hands-on Microsoft workshop led by women trainers for women where you will learn how to build a Dashboard in a Day! #### Power Platform Conference-Power BI and Fabric Sessions

Join us Oct 1 - 6 in Las Vegas for the Microsoft Power Platform Conference. Top Solution Authors
Top Kudoed Authors
Users online (2,526)