Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

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

Reply
e9801591
Helper I
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

2020-02-21_13-22-39.png

 

 

 

 

 

Thanks in advance!

 

1 ACCEPTED SOLUTION

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!

View solution in original post

5 REPLIES 5
danextian
Super User
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.

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

DateItemInventory Qty
1/7/2020Item A100
1/7/2020Item B20
1/28/2020Item A50
1/28/2020Item B10

@e9801591 

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] )))

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!

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.

Helpful resources

Announcements
LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

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

April Fabric Community Update

Fabric Community Update - April 2024

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