Power BI Exchange

Please login or click SIGN UP FOR FREE to create your PowerBIUG account to join this user group.
Expand all | Collapse all

Cost of goods sold using average method

Jump to Best Answer
  • 1.  Cost of goods sold using average method

    Posted 29 days ago
    Edited by Makhdoom Liaqat 29 days ago

    Hi,

    I am working on cost of goods sold using average method.

     

    GName Year Opening Stock InQty OutQty Closing Stock InItemValue Average Value Closing Stock Value Opening Stock Value Cost of Goods Sold
    Bahria Town 2016 4454 3586 868 126610299.8 28426.20113 24673942.58 0 101936357.2
    Bahria Town 2017 868 6379 6547 700 166903971.5 23030.76743 16121537.2 0


    i am calculating first Average Value Which is 

    Average Value = (([initemvaluee]+[Opening Stock Value])/([inqtyy]+[Opening Stock]))



    Now i want the Opening Stock Value which should be closing stock value of last year which is  "24673942.58" Please suggest if i am working wrong. On opening stock value i am calculating 

    Opening Stock Value = CALCULATE([Closing Stock Value],SAMEPERIODLASTYEAR('date'[Date])

    but it is giving error. Please help me out.

    i am also attaching the file for further if you need.

    Cost of goods sold (mediafire.com)



    ------------------------------
    Makhdoom Liaqat
    Administrator
    ------------------------------


  • 2.  RE: Cost of goods sold using average method

    Top Contributor
    Posted 29 days ago
    Hello:

    DAX has OPENINGBALANCEYEAR, CLOSINGBALANCEYEAR (same for month and quarter).

    You can write it like so   = OPENINGBALANCEYEAR([Closing Stock], Dates[Date])

    I believe these functions will help you.

    ------------------------------
    William Skelley
    01106
    Longmeadow MA
    8602807221
    ------------------------------



  • 3.  RE: Cost of goods sold using average method

    Posted 29 days ago
    Oh Sorry,
    Opening Stock Value = CALCULATE([Closing Stock value],SAMEPERIODLASTYEAR('date'[Date].[Date])​

    Basically when i add this it give me error of depndancy. 

    please help



    ------------------------------
    Makhdoom Liaqat
    Administrator
    ------------------------------



  • 4.  RE: Cost of goods sold using average method
    Best Answer

    Top Contributor
    Posted 29 days ago
    Did you try:


     = OPENINGBALANCEYEAR([Closing Stock Value], Dates[Date])
     = CLOSINGBALANCEYEAR([Closing Stock Value], Dates[Date])

    You can also use CLOSINGBALANCEYEAR / Month/ Quarter etc.

    I would have a separate Date Table marked as such too. 


    ------------------------------
    William Skelley
    01106
    Longmeadow MA
    8602807221
    ------------------------------



  • 5.  RE: Cost of goods sold using average method

    Posted 29 days ago

    i tried both of them and same error i got

    A circular dependency was detected: Measure: 'mak_stockInHandValue'[Opening Stock Value], Measure: 'mak_stockInHandValue'[Closing Stock Value], Measure: 'mak_stockInHandValue'[Average Value], Measure: 'mak_stockInHandValue'[Opening Stock Value].


    ------------------------------
    Makhdoom Liaqat
    Administrator
    ------------------------------



  • 6.  RE: Cost of goods sold using average method

    Posted 29 days ago


    ------------------------------
    Makhdoom Liaqat
    Administrator
    ------------------------------