Skip to main content
cancel
Showing results for 
Search instead 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

Reply
weinerheineken
Frequent Visitor

Create a Quantity On Hand measure

I'd like to create a measure that computes the stock on hand. But the sum of the stock has meaning only per day. For any selected range of dates, it should only take the sum of the values in the last date in context.

I mean if I select a date, let's say 2023-01-22, I should get the stock for this particular day. But if I select a range like 2022-01-01 to 2022-12-31, it should return the sum of the values only for the last date which is in this case 2022-12-31.

 

I have two tables:

- Inventory: Warehouse ID, Product ID, Quantity On Hand, Date Key ( format YYYYMMDD)

- Dates: Date Key ( format YYYYMMDD ), Date ( date format)

 

The two tables are linked by the Date Key column.

 

I really need help, please.

1 ACCEPTED SOLUTION
FreemanZ
Super User
Super User

hi @weinerheineken 

 

try like
stock on hand =
VAR _date = MAX(Dates[Date Key])
RETURN
CALCULATE(
    SUM(Inventory[Quantity On Hand]),
    Dates[Date Key] = _date
)

View solution in original post

1 REPLY 1
FreemanZ
Super User
Super User

hi @weinerheineken 

 

try like
stock on hand =
VAR _date = MAX(Dates[Date Key])
RETURN
CALCULATE(
    SUM(Inventory[Quantity On Hand]),
    Dates[Date Key] = _date
)

Helpful resources

Announcements
Europe Fabric Conference

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 Carousel June 2024

Power BI Monthly Update - June 2024

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

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.