Forum Discussion

Prabhu11's avatar
Prabhu11
Frequent Visitor
3 years ago
Solved

Ignore zeros while calculating closing stock value

Hi Community,

 

I'm facing a challenge in calculating the last closing value, kindly help

 

The following table represents the data model I'm using, 

 

my requirement is to return a non-zero value from the last month of the time period selected from the slicer,

For example, if a user selects a date range from 01 Jan 2022 to 31 March 2022.

 

Expected Closing Stock output = 130

Expected Closing Stock target = 110

 

DatesClosing stock  actualTarget
01-01-221000
31-01-22090
01-02-22900
28-02-220100
01-03-221300
31-03-220110

 

I tried following formulas

1.  CALCULATE ( SUM (Closing stock actual) ), LASTDATE(Date'[dates]')

         Here is the problem I faced, PBI returned 0 as result for actual.  Target working fine because it consists of value in the last date.

 

2. CLOSINGBALANCEMONTH ( SUM(Closing stock),    Date'[dates]')
       Here also the same issue, PBI returned 0 as the result for actual. 
 
Kindly help,
Thank You
Need Help

3 Replies

  • Bifinity_75's avatar
    Bifinity_75
    Icon for Solution Sage rankSolution Sage
    3 years ago

    Hi Prabhu11 try with this measure:

    Mesure = 
CALCULATE(max('Table'[Closing Stock] ), 
    filter('Table','Table'[Closing Stock]<>0),'Table'[Date]<=max('Table'[Date]))

    The result:

    Best regards

  • Prabhu11's avatar
    Prabhu11
    Frequent Visitor
    3 years ago

    Hi Bifinity,

     

    Thanks for the response,

    I tried the solution you suggested but that did not work as I expected. Kindly check the attached screenshot.

     

    Expected Output = 130 (even if I select the date range up to 31st March 2022)

     

     

    Regards,

    Prabhu

    • Anonymous's avatar
      Anonymous
      Not applicable
      3 years ago

      Hi Prabhu11 ,

       

      Please try:

      Measure = 
VAR _MAX_DATE=CALCULATE(MAX('Table'[Dates]),'Table'[Dates]<=MAX('Table'[Dates])&&'Table'[Closing stock  actual]<>0)
VAR _VALUE = CALCULATE(MAX('Table'[Closing stock  actual]),'Table'[Dates]=_MAX_DATE)
RETURN
_VALUE

      Best Regards,
      Gao
      Community Support Team

       

      If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

      How to get your questions answered quickly -- How to provide sample data