Forum Discussion
Last Non zero Value based on max date
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 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.
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 RegardsTry all instead of allexcept in filter section.
Thanks & regards,
Pravin Wattamwar.
If it resolves your problem mark it as solution and give kudos.
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
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?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