Forum Discussion

rajasekar_o's avatar
rajasekar_o
Icon for Helper V rankHelper V
2 years ago
Solved

opening stock

hi team, i need Help 

i have stock data 

 

with out date filter its shows correct value . if am applay date filter opening Quantity not Show
(as per date 02-04-2019 have opening qty is 19.10) like this am giveing any date opening quantity not show

am used opening quantity calculation

opening quantity =
CALCULATE(
    [rec qty]-[issue qty],
    FILTER(
        ALL('STOCKVALUE'[DOCDATE]),
        'STOCKVALUE'[DOCDATE] + 1 <= MAX('STOCKVALUE'[DOCDATE])
    )
)
 

how to solve this problem.

Need Help
Tips and Tricks
  • danextian's avatar
    danextian
    2 years ago

    hI rajasekar_o ,

     

    This is one of the reasons why, as a good practice, to use a seprate dates table - this simplifies time intelligence calculations. If you don't have one yet you can import or create one in M or DAX (using CALENDAR function). If you already have one and have setup a relationship to your fact table, try this formula

    =
CALCULATE (
    [rec qty] - [issue qty],
    FILTER ( ALL ( DatesTable ), DatesTable[Date] < MIN ( DatesTable[Date] ) )
)

     

    This tells Power BI to get the running value of [rec qty]-[issue qty] before the date in the current filter context which, based on yoru sample table, is before any of the dates in the Date column. Closing is simply <=MAX ( DatesTable[Date] )

     

    If  this doesn't work for you, please post a  sample data that can be copy-pasted (not an image) or sanitized copy of your pbix (confidential data removed). You can post a link to a file in the cloud.

     

1 Reply

  • danextian's avatar
    danextian
    Icon for Super User rankSuper User
    2 years ago

    hI rajasekar_o ,

     

    This is one of the reasons why, as a good practice, to use a seprate dates table - this simplifies time intelligence calculations. If you don't have one yet you can import or create one in M or DAX (using CALENDAR function). If you already have one and have setup a relationship to your fact table, try this formula

    =
CALCULATE (
    [rec qty] - [issue qty],
    FILTER ( ALL ( DatesTable ), DatesTable[Date] < MIN ( DatesTable[Date] ) )
)

     

    This tells Power BI to get the running value of [rec qty]-[issue qty] before the date in the current filter context which, based on yoru sample table, is before any of the dates in the Date column. Closing is simply <=MAX ( DatesTable[Date] )

     

    If  this doesn't work for you, please post a  sample data that can be copy-pasted (not an image) or sanitized copy of your pbix (confidential data removed). You can post a link to a file in the cloud.