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 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
Anonymous I'm getting below output. I guess this is what you were expecting.
Hi Anonymous
Yes that is exactly what i am after!
Have i done sopmething wrong within the formula?
Kind Regards