Forum Discussion

rajasekaro's avatar
rajasekaro
Helper III
1 year ago
Solved

opening closing

hi team 
i have stock data 

i calculate opening closing qty 

Opening Qty =
var mindate = MIN('CALENDAR'[Date])
var maxdate = MAX('CALENDAR'[Date])
RETURN
CALCULATE(
    CALCULATE (
    SUMX (
        FILTER (
            'F4602 stock value',
            'F4602 stock value'[Docdate] <= maxdate
        ),
        IF ( 'F4602 stock value'[TRTY] = "RCPT",'F4602 stock value'[TRQT], -'F4602 stock value'[TRQT])
    )
),
    WINDOW(1,ABS,-1,REL,ALL('F4602 stock value'[Docdate],'F4602 stock value'[DOCO]), ORDERBY ( 'F4602 stock value'[Docdate], ASC)
))

 

Received Qty =
var mindate = MIN('CALENDAR'[Date])
var maxdate = MAX('CALENDAR'[Date])
RETURN
CALCULATE(
    SUM('F4602 stock value'[TRQT]),
    FILTER('F4602 stock value','F4602 stock value'[TRTY]="RCPT" && 'F4602 stock value'[Docdate]>=mindate && 'F4602 stock value'[Docdate]<=maxdate))

 

Issue Qty =
var mindate = MIN('CALENDAR'[Date])
var maxdate = max('CALENDAR'[Date])
RETURN
CALCULATE(
    SUM('F4602 stock value'[TRQT]),
    FILTER('F4602 stock value','F4602 stock value'[TRTY]= "ISSU" && 'F4602 stock value'[Docdate]>=mindate && 'F4602 stock value'[Docdate]<=maxdate))

Closing_Qty_New = SUMX(
    VALUES('Measure'),
    ([Opening Qty]+[Received Qty]-[Issue Qty])
)

i i dint select date its shoing correct value 

if i select between date its showing worng value 

how to solve this 



Need Help
Tips and Tricks
Tutorial Requests
  • v-venuppu's avatar
    v-venuppu
    11 months ago

    Hi rajasekaro ,

    I have created a sample PBIX file using the given sample data.Please go through the attached file for your reference.If it doesn't meet the requirement, kindly share the expected output, so that will work on it accordingly.

    Thank you.

9 Replies

  • FBergamaschi's avatar
    FBergamaschi
    Super User
    1 year ago

    Please can you clarify what is right and wrong in the pictures (selecting or not dates)?

     

    Furthermore, we need to see your model and data, so

     

    Please include, in a usable format, not an image, a small set of rows for each of the tables involved in your request and show the data model in a picture, so that we can import the tables in Power BI and reproduce the data model. The subset of rows you provide, even is just a subset of the original tables, must cover your issue or question completely. Alternatively, you can share your .pbix via some cloud service and paste the link here. Do not include sensitive information and do not include anything that is unrelated to the issue or question. Please show the expected outcome based on the sample data you provided and make sure, in case you show a Power BI visual, to clarify the columns used in the grouping sections of the visual.

     

    Need help uploading data? click here

     

    Want faster answers? click here

     

    • rajasekaro's avatar
      rajasekaro
      Helper III
      1 year ago

      FBergamaschi 
      stockvalue table

      ItemDocdateQTYTYPE
      BP Monitor19-03-2024150RCPT
      BP Monitor17-04-202410ISSU
      BP Monitor01-07-2024150RCPT
      BP Monitor03-07-2024100ISSU
      BP Monitor04-07-2024300RCPT
      BP Monitor02-12-2024100ISSU
      BP Monitor27-12-2024250RCPT
      BP Monitor21-01-2025250RCPT
      BP Monitor19-02-2025150RCPT
      BP Monitor13-03-2025100RCPT
      BP Monitor15-04-2025250RCPT
      BP Monitor30-04-2025200ISSU
      BP Monitor05-07-2025300ISSU
      BP Monitor21-08-2025200RCPT
  • v-venuppu's avatar
    v-venuppu
    Community Support
    1 year ago

    Hi rajasekaro ,

    Thank you for reaching out to Microsoft Fabric Community.

    Thank you FBergamaschi for the prompt response.

    Thanks for sharing the sample data and data model, can you also please share the expected output like what you are expecting excatly with date to be selected or not selected as already requested by FBergamaschi , so that it will be helpful for us to solve the issue.

  • v-venuppu's avatar
    v-venuppu
    Community Support
    11 months ago

    Hi rajasekaro ,

    I have created a sample PBIX file using the given sample data.Please go through the attached file for your reference.If it doesn't meet the requirement, kindly share the expected output, so that will work on it accordingly.

    Thank you.

  • v-venuppu's avatar
    v-venuppu
    Community Support
    11 months ago

    Hi rajasekaro ,

    I wanted to check if you had the opportunity to review the information provided and resolve the issue..?Please let us know if you need any further assistance.We are happy to help.

    Thank you.

  • v-venuppu's avatar
    v-venuppu
    Community Support
    11 months ago

    Hi rajasekaro ,

    May I ask if you have resolved this issue? Please let us know if you have any further issues, we are happy to help.

    Thank you.

  • v-venuppu's avatar
    v-venuppu
    Community Support
    11 months ago

    Hi rajasekaro ,

    I hope the information provided is helpful.I wanted to check whether you were able to resolve the issue with the provided solutions.Please let us know if you need any further assistance.

    Thank you.