cancel
Showing results for
Did you mean:

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Helper I

## Get Beginning of the Month (earliest date with value) inventory and display current inventory

Hi. I am currently having trouble on creating a measure to be able to show the beginning of the month inventory. The objective is that the table would show the existing inventory based on the date selected (ex. Jan 28) as well as the beginning of the month inventory based on the earliest date (with values), which is based on the month selected.

Need help on how to create a working measure 'Beg of the Month Inventory".  It should show inventory for 1/7/2020 which is 38K and ignore the Date Slicer. Image below

1 ACCEPTED SOLUTION
Helper I

Thank you for everyone's help! I took some pieces from the advise and came up with this

Beg of Month Inventory =

VAR starting = CALCULATE(FIRSTDATE('Table'[Date]), ALL('DATE TABLE'[Date]))
RETURN
CALCULATE(SUM('Table'[Inventory Qty]),FILTER(ALL('DATE TABLE'[Date]),'DATE TABLE'[Date]=starting))

Works now. Thank you!

5 REPLIES 5
Super User

Hi @e9801591 ,

First off, how do you compute for the inventory amount? And please post a sample data.

If Inventory was a balance sheet account, I would compute for its cumulative total from the earliest transaction date so in DAX it would be something like

``````nventory Balance =
CALCULATE (
SUM ( JournalsTable[Amount] ),
FILTER (
ALL ( DatesTable[Date] ),
DatesTable[Date] <= MAX ( DatesTable[Date] )
)
)
``````

then the beginning inventory would be the cumulative total prior to the current period/date

``````Beginning Inventory Balance =
CALCULATE (
SUM ( JournalsTable[Amount] ),
FILTER (
ALL ( DatesTable[Date] ),
DatesTable[Date] < MIN ( DatesTable[Date] )
)
)
``````

But this may not be the case for you.

Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

"Tell me and I’ll forget; show me and I may remember; involve me and I’ll understand."
Need Power BI consultation, get in touch with me on LinkedIn or hire me on UpWork.
Learn with me on YouTube @DAXJutsu or follow my page on Facebook @DAXJutsuPBI.
Helper I

Thank you for the quick reply @danextian . My source table contains Inventory count per day so my Beginning of the month Inventory would just be a sum of Inventory Qty for that day (earliest date with value) for the month.

Example data

 Date Item Inventory Qty 1/7/2020 Item A 100 1/7/2020 Item B 20 1/28/2020 Item A 50 1/28/2020 Item B 10
Super User

I hope the above solution would have solved it. If not try

``````Start of month =calculate(sum(Table[Qty]),Filter('Date',Date[Date] = startofmonth(Date[Date] )))
End of month =calculate(sum(Table[Qty]),Filter('Date',Date[Date] = endofmonth(Date[Date] )))``````
Helper I

Thank you for everyone's help! I took some pieces from the advise and came up with this

Beg of Month Inventory =

VAR starting = CALCULATE(FIRSTDATE('Table'[Date]), ALL('DATE TABLE'[Date]))
RETURN
CALCULATE(SUM('Table'[Inventory Qty]),FILTER(ALL('DATE TABLE'[Date]),'DATE TABLE'[Date]=starting))

Works now. Thank you!

Super User

Hi @e9801591 ,

Some other users might have another takes on this but here's mine:

First, create a calculated column to index the dates per inventory item.

``````Date Index Per Item =
RANKX (
VALUES ( Inventory[Date] ),
CALCULATE (
MIN ( 'Inventory'[Date] ),
ALLEXCEPT ( 'Inventory', 'Inventory'[Date] ),
Inventory[Item] = EARLIER ( Inventory[Item] )
),
,
ASC,
DENSE
)
``````

Second, you may either create a calculated column for the inventory value the immediately preceding day in the table or create a measure

``````Inventory Qty Prev Day Column =
CALCULATE (
SUM ( Inventory[Inventory Qty] ),
FILTER (
ALL ( Inventory ),
Inventory[Item] = EARLIER ( Inventory[Item] )
&& Inventory[Date Index Per Item]
= EARLIER ( Inventory[Date Index Per Item] ) - 1
)
)

Inventory Qty Prev Day Measure =
SUMX (
SUMMARIZE (
Inventory,
Inventory[Item],
Inventory[Date Index Per Item],
"Value", CALCULATE (
SUM ( Inventory[Inventory Qty] ),
FILTER (
ALL ( Inventory ),
Inventory[Item] = EARLIER ( Inventory[Item] )
&& Inventory[Date Index Per Item]
= EARLIER ( Inventory[Date Index Per Item] ) - 1
)
)
),
[Value]
)
``````

Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

"Tell me and I’ll forget; show me and I may remember; involve me and I’ll understand."
Need Power BI consultation, get in touch with me on LinkedIn or hire me on UpWork.
Learn with me on YouTube @DAXJutsu or follow my page on Facebook @DAXJutsuPBI.

Announcements

#### Fabric certifications survey

Certification feedback opportunity for the community.

#### Power BI Monthly Update - April 2024

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

#### Fabric Community Update - April 2024

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

Top Solution Authors
Top Kudoed Authors