Forum Discussion
Helper IIISales and Qty on Hand
Hello, I have two tables store sales and inventory that I'm needing to pull data from. Inventory is given by date so I've filtered to day 7 to get the ending balance for the week. My sales table lists sales by date so filtering on day 7 I only get one days sales. I need to create a measure to calculate the total sales by week number. Below is what I have tried but it doesn't seem to work and recalculates the inventory number:
jimrosser See attached PBIX file. I'm having an issue matching your desired numbers with your sample data.
7 Replies
- Greg_Deckler
Community Champion
jimrosser Sorry, having trouble following, can you post sample data as text and expected output?
Not really enough information to go on, please first check if your issue is a common issue listed here: https://community.powerbi.com/t5/Community-Blog/Before-You-Post-Read-This/ba-p/1116882
Also, please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490
The most important parts are:
1. Sample data as text, use the table tool in the editing bar
2. Expected output from sample data
3. Explanation in words of how to get from 1. to 2.
jimrosserGreg - thanks for the response. Can you tell me how to create another filter in the measure below? I'd like for it to filter on date and Item Number.
Wkly Sales =CALCULATE (SUM ( 'store_sales'[ty_qty] ),FILTER ( ALL ( 'store_sales' ), 'store_sales'[wm_item_nbr] <= MAX ( 'store_sales'[wm_item_nbr] ) ),VALUES ( 'store_sales'[yr_wk_nbr] ))- Greg_Deckler
jimrosser Completely flying blind without sample data but here's a shot in the dark:
Wkly Sales = CALCULATE ( SUM ( 'store_sales'[ty_qty] ), FILTER ( ALL ( 'store_sales' ), 'store_sales'[wm_item_nbr] <= MAX ( 'store_sales'[wm_item_nbr] ) && 'store_sales'[date] = DATE(2024,2,2) ), VALUES ( 'store_sales'[yr_wk_nbr] ) )