Last Non zero Value based on max date

[Anonymous]

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, 

UnitId	ChargeAmount	StartDate
78268	84.25	01/06/2006 00:42
78268	87.7	02/04/2007 00:00
78268	91.56	07/04/2008 00:00
78268	96.6	06/04/2009 00:00
78268	94.99	05/04/2010 00:00
78268	97.83	04/04/2011 00:00
78268	101.8	02/04/2012 00:00
78268	104.77	01/04/2013 00:00
78268	108.64	07/04/2014 00:00
78268	108.64	14/07/2014 00:00
78268	0	18/05/2015 17:42

Any help would be great. 

Kind Regards

[Anonymous · Accepted solution]

@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]

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]

@Anonymous Okay Let's start finding the cause. Are you getting expected output with the sample data you have shared?
Can you please share one example where in you are not getting expected result?

[Anonymous]

Hi  @Anonymous 

It might be easier to give you more date to play with. This time i have provided data for 4 unitIds which have a series of rents, one of which has their latest rent charge at zero so we would want the one before. 

UnitId	ChargeAmount	StartDate
78264	70	01/06/2006 00:42
78266	96.46	01/06/2006 00:42
78268	84.25	01/06/2006 00:42
78269	90.82	01/06/2006 00:42
78264	72.87	02/04/2007 00:00
78266	100.41	02/04/2007 00:00
78268	87.7	02/04/2007 00:00
78269	94.54	02/04/2007 00:00
78269	94.54	14/01/2008 00:00
78264	78.08	07/04/2008 00:00
78266	104.83	07/04/2008 00:00
78268	91.56	07/04/2008 00:00
78269	98.7	07/04/2008 00:00
78264	84.37	06/04/2009 00:00
78266	110.6	06/04/2009 00:00
78268	96.6	06/04/2009 00:00
78269	100.08	06/04/2009 00:00
78264	78.34	17/08/2009 00:00
78266	109.45	05/04/2010 00:00
78268	94.99	05/04/2010 00:00
78269	98.08	05/04/2010 00:00
78266	113.03	04/04/2011 00:00
78268	97.83	04/04/2011 00:00
78269	102.08	04/04/2011 00:00
78264	85.12	02/04/2012 00:00
78266	117.93	02/04/2012 00:00
78268	101.8	02/04/2012 00:00
78269	106.31	02/04/2012 00:00
78264	89.76	01/04/2013 00:00
78266	121.01	01/04/2013 00:00
78268	104.77	01/04/2013 00:00
78269	107.61	01/04/2013 00:00
78264	95.08	07/04/2014 00:00
78266	125.49	07/04/2014 00:00
78268	108.64	07/04/2014 00:00
78269	110.44	07/04/2014 00:00
78268	108.64	14/07/2014 00:00
78264	97.17	06/04/2015 00:00
78266	128.25	06/04/2015 00:00
78269	112.87	06/04/2015 00:00
78268	0	18/05/2015 17:42
78264	96.2	04/04/2016 00:00
78266	126.97	04/04/2016 00:00
78269	111.74	04/04/2016 00:00
78264	95.23	03/04/2017 00:00
78266	125.7	03/04/2017 00:00
78269	110.62	03/04/2017 00:00
78264	94.27	02/04/2018 00:00
78266	124.44	02/04/2018 00:00
78269	109.51	02/04/2018 00:00

The Measure i have created using your formula was ;

Measure from Vimal =

VAR _maxdate = CALCULATE(MAX(RentUnitCharge[StartDate]),FILTER(ALLEXCEPT(RentUnitCharge,RentUnitCharge[UnitId]),RentUnitCharge[ChargeAmount]>0))

RETURN CALCULATE(MAX(RentUnitCharge[ChargeAmount]),RentUnitCharge[StartDate]=_maxdate)

I also have added the example which @Anonymous suggested by changing to ALL instead of ALLEXCEPT

Measure from Pr20048119 =

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

RETURN CALCULATE(MAX(RentUnitCharge[ChargeAmount]),RentUnitCharge[StartDate]=_maxdate)

My Dashboard is using two methods to display the measure, one being a table for granula level and the other a matrix table .

The idea was to then use this new last charged rent to calculate an average weekly rent figure, as they come in different frequencies. 

Let me know if there is anything else i can give you. 

Thanks again for helping. 

Kind Regards

[Anonymous]

[Anonymous]

@Anonymous I'm getting below output. I guess this is what you were expecting.

[Anonymous]

Hi @Anonymous 

Yes that is exactly what i am after!

Have i done sopmething wrong within the formula?

Kind Regards

[Anonymous]

For closure purposes and in case anyone else has the same issue,

@Anonymous solution works, it was my fault for leaving my test measure within the table/matrix. Once these were removed the new measure works.

Thanks again @Anonymous  for your help.

[Anonymous]

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]

@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