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 based on the latest date, the part im struggling with is if the returned value is zero i would like the last non zero amount. 

 

Last Rent Charged = CALCULATE(MAX(RentUnitCharge[ChargeAmount]),FILTER(RentUnitCharge,RentUnitCharge[StartDate] = MAX(RentUnitCharge[StartDate])))

 

example data of the issue, 

UnitIdChargeAmountStartDate
7826884.2501/06/2006 00:42
7826887.702/04/2007 00:00
7826891.5607/04/2008 00:00
7826896.606/04/2009 00:00
7826894.9905/04/2010 00:00
7826897.8304/04/2011 00:00
78268101.802/04/2012 00:00
78268104.7701/04/2013 00:00
78268108.6407/04/2014 00:00
78268108.6414/07/2014 00:00
78268018/05/2015 17:42

 

Any help would be great. 

 

Kind Regards

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. 

10 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    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.