Forum Discussion

Olwin's avatar
Olwin
Frequent Visitor
4 years ago

Inventory Beginning and Ending Balance

Hi there,   I want to create a measure that shows Beginning Balance and Ending Balance of my Inventory. I have 2 tables: Master Item and Inventory Transaction. Here are my sample data:   Master...
Need Help
  • selimovd's avatar
    selimovd
    Icon for Most Valuable Professional rankMost Valuable Professional
    4 years ago

    Hey Olwin ,

     

    in this case you have to change the posting date to equal the exact minimum date, like this:

    BegBal =
VAR first_date = [FirstDateVisible]
RETURN
    CALCULATE(
        SUM( VE[Cost Amount] ),
        VE[Posting Date] = first_date
    )

     

    Also you have to make sure your the relationship between the Dates and the fact table has to be 1:n with single filter direction:

     

    Then it should work like you want.

     

    If you need any help please let me know.
    If I answered your question I would be happy if you could mark my post as a solution ✔️ and give it a thumbs up 👍
     
    Best regards
    Denis
     
    • Olwin's avatar
      Olwin
      Frequent Visitor
      4 years ago

      Hi selimovd ,

       

      I still unable to get my expected result.

      My founding is I need to create 1 more date table and not to relate it in any table, just to get the "Start Date" and "End Date" for all records with the same value on my date slicer.

      Please find below link to find my revised pbix file.

      I add new measure FirstDateVisibleNEW and LastDateVisibleNEW which refer to my new Date table. (You can see that this 2 measure is the same value as my date slicer).

       

      Kindly please advise if there is any other solution to achive this 🙂

      Thank you.

       

      https://smesolution-my.sharepoint.com/:u:/g/personal/olwin_smesolution_onmicrosoft_com/EYZh623QdylBl7SfeebmrvcBNXtRFvXk4uCENSv6R0l7-w?e=dcPc2R 

      • selimovd's avatar
        selimovd
        Icon for Most Valuable Professional rankMost Valuable Professional
        4 years ago

        Hey Olwin ,

         

        what exactly is not working?

        You don't need a second date table that is not connected to any other tables. As I mentioned in the last reply, you have to set the filter direction between date table and VE table to "single". Then the report is working properly.

         

        If you need any help please let me know.
        If I answered your question I would be happy if you could mark my post as a solution ✔️ and give it a thumbs up 👍
         
        Best regards
        Denis