Forum Discussion
Helper IIIopening stock mistake
hi team
i calculate opening stock and closing
if i select date , opening & closing not comming correctly
Hi rajasekaro,
Thank you for reaching out to the Microsoft fabric community forum. Also, thanks to FarhanJeelani, Shahid12523, for those inputs on this thread. I reproduced the scenario, and it worked on my end. I used my sample data and successfully implemented it.
I am also including .pbix file for your better understanding, please have a look into it:
Hope this helps clarify things and let me know what you find after giving these steps a try happy to help you investigate this further.
Thank you for using the Microsoft Fabric Community Forum.
10 Replies
- FarhanJeelani
Super User
Hi rajasekaro ,
The issue is almost certainly in how the opening stock is calculated when you filter by date.
Root cause:
Your Opening Qty currently uses Docdate <= maxdate. That includes all transactions up to the end of the selected range, not just the period before it. So when you pick a date, OpeningQty can include transactions from that date as well, which breaks the opening balance logic.
What to change:Opening balance should be computed up to (but not including) the start of the selected date range.
Then Closing = Opening + Received - Issue works if Opening/Received/Issue are correct.
Recommended DAX (measures, not calculated columns)Opening Qty (as of the day before the start date)
This gives the opening balance for the period defined by the current calendar filter.
Opening Qty = VAR StartDate = MIN('CALENDAR'[Date]) RETURN CALCULATE( SUMX( 'F4602 stock value', IF('F4602 stock value'[TRTY] = "RCPT", 'F4602 stock value'[TRQT], -'F4602 stock value'[TRQT]) ), FILTER( ALL('F4602 stock value'), 'F4602 stock value'[Docdate] < StartDate ) )Notes:
This uses Docdate < StartDate so you’re counting only transactions before the period starts.
If you need to preserve item/branch context, add the appropriate ALLEXCEPT or use ALLSELECTED in the FILTER to keep you in the same group.
Received Qty (within the selected date range) Received Qty = CALCULATE( SUM('F4602 stock value'[TRQT]), 'F4602 stock value'[TRTY] = "RCPT", 'F4602 stock value'[Docdate] >= MIN('CALENDAR'[Date]), 'F4602 stock value'[Docdate] <= MAX('CALENDAR'[Date]) )Issue Qty (within the selected date range) Issue Qty = CALCULATE( SUM('F4602 stock value'[TRQT]), 'F4602 stock value'[TRTY] = "ISSU", 'F4602 stock value'[Docdate] >= MIN('CALENDAR'[Date]), 'F4602 stock value'[Docdate] <= MAX('CALENDAR'[Date]) )
Closing Qty
If Opening Qty, Received Qty, and Issue Qty are measures, you can simply:
Closing Qty = [Opening Qty] + [Received Qty] - [Issue Qty]If you need to aggregate across some dimension in a visual, you can still use Closing Qty as a single measure; avoid using SUMX unless you’re iterating over a dimension and need per-row sums.
Please mark this post as solution if it helps you. Appreciate Kudos
- rajasekaro
Opening Qty = VAR StartDate = MIN('CALENDAR'[Date]) RETURN CALCULATE( SUMX( 'F4602 stock value', IF('F4602 stock value'[TRTY] = "RCPT", 'F4602 stock value'[TRQT], -'F4602 stock value'[TRQT]) ), FILTER( ALL('F4602 stock value'), 'F4602 stock value'[Docdate] < StartDate ) )
ITS not working ,all days opening showing blank how to fix this- FarhanJeelani
Hi rajasekaro ,
The issue is likely coming from using MIN('CALENDAR'[Date]) as the as-of date. In many visuals, MIN can be BLANK or not reflect the date of the current row, which makes the filter Docdate < StartDate evaluate to BLANK and your measure returns blanks for all days.
Use the date of the current context (as-of date) with MAX('CALENDAR'[Date]) and guard against BLANKs.:
Opening Qty = VAR AsOfDate = MAX('CALENDAR'[Date]) VAR Opening = CALCULATE( SUMX( 'F4602 stock value', IF( 'F4602 stock value'[TRTY] = "RCPT", 'F4602 stock value'[TRQT], -'F4602 stock value'[TRQT] ) ), FILTER( ALL('F4602 stock value'), 'F4602 stock value'[Docdate] < AsOfDate ) ) RETURN IF( ISBLANK(AsOfDate), BLANK(), Opening )
- Shahid12523
Community Champion
- Opening Qty is incorrect because you're including transactions up to max date, instead of before min date.
- Replace it with:
Opening Qty =
VAR mindate = MIN('CALENDAR'[Date])
RETURN
CALCULATE (
SUMX (
'F4602 stock value',
IF (
'F4602 stock value'[Docdate] < mindate,
IF('F4602 stock value'[TRTY] = "RCPT", [TRQT], -[TRQT]),
0
)
)
)
- Closing Qty will then work correctly as:
Closing Qty = [Opening Qty] + [Received Qty] - [Issue Qty]- rajasekaro
not working it showing 0
v-kpoloju-msftCommunity Support
Hi rajasekaro,
Thank you for reaching out to the Microsoft fabric community forum. Also, thanks to FarhanJeelani, Shahid12523, for those inputs on this thread. I reproduced the scenario, and it worked on my end. I used my sample data and successfully implemented it.
I am also including .pbix file for your better understanding, please have a look into it:
Hope this helps clarify things and let me know what you find after giving these steps a try happy to help you investigate this further.
Thank you for using the Microsoft Fabric Community Forum.- v-kpoloju-msft
Hi rajasekaro,
Just checking in to see if the issue has been resolved on your end. If the earlier suggestions helped, that’s great to hear! And if you’re still facing challenges, feel free to share more details happy to assist further.Thank you.
- v-kpoloju-msft
Hi rajasekaro,
Hope you had a chance to try out the solution shared earlier. Let us know if anything needs further clarification or if there's an update from your side always here to help.Thank you.
