cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

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

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - August 2024

Check out the August 2024 Power BI update to learn about new features.

#### Fabric Community Update - August 2024

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

Top Solution Authors
Top Kudoed Authors