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 va...
Need Help
  • Anonymous's avatar
    Anonymous
    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

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