Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Last Non zero Value based on max date

Hi All   I am trying to get the last charged amount to an id which im running into the issue of getting zeros where the last entry is a zero.   My current formula only looks at the max value base...
Help required
  • Anonymous's avatar
    Anonymous
    6 years ago

    Anonymous Please try below measure. 

    Measure = 
VAR _maxdate = CALCULATE(MAX('Table'[StartDate]),FILTER(ALLEXCEPT('Table','Table'[UnitId]),'Table'[ChargeAmount]>0))
RETURN CALCULATE(MAX('Table'[ChargeAmount]),'Table'[StartDate]=_maxdate)

    If it helps accept as solution. 

Anonymous's avatar
Anonymous
Not applicable
6 years ago

Hi Anonymous 

 

I have tried to use your solution but it doesnt seem to present any values. 

 

Measure =
VAR _maxdate = CALCULATE(MAX(RentUnitCharge[StartDate]),FILTER(ALLEXCEPT(RentUnitCharge,'RentUnitCharge'[UnitId]),RentUnitCharge[ChargeAmount]>0))
RETURN CALCULATE(MAX(RentUnitCharge[ChargeAmount]),RentUnitCharge[StartDate]=_maxdate)
 
Have i done this correctly?
 
Kidn Regards
Anonymous's avatar
Anonymous
Not applicable
6 years ago

Try  all instead of allexcept in filter section.

 

 

Thanks & regards,

Pravin Wattamwar.

 

If it resolves your problem mark it as solution and give kudos.

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    Anonymous  Hi Sorry this still hasnt sorted it, unless i havent done it correctly.

     

    Measure =
    VAR _maxdate = CALCULATE(MAX(RentUnitCharge[StartDate]),FILTER(ALL(RentUnitCharge),RentUnitCharge[ChargeAmount]>0))
    RETURN CALCULATE(MAX(RentUnitCharge[ChargeAmount]),RentUnitCharge[StartDate]=_maxdate)
     
    Kind Regards