cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Anonymous
Not applicable

## Finding date for last non zero value in a table

I'm looking to build a measure that returns the last date where an hour is forecast.  I have to have 0's in the forecast for a burndown chart measure I'm using otherwise I get odd spikes.

Currently using

Burndown = A_F_COMBINED_DATA[ForecastTotal]-calculate(sum(A_F_COMBINED_DATA[Hours]),FILTER(ALL(A_F_COMBINED_DATA[Date]),A_F_COMBINED_DATA[Date] <= Max (A_F_COMBINED_DATA[Date])))

ForecastTotal = calculate(SUM(A_F_COMBINED_DATA[Hours]),ALL(A_F_COMBINED_DATA[Hours],A_F_COMBINED_DATA[Date]))

I then use this:

ForecastMaxDate = Max(A_F_COMBINED_DATA[Date])

But will get the last date in the whole data set as 0 is read as a positive.

What I'm looking for is the last non-zero value and return the date associated with it.

So for the below example, A would be 18/12/19

B would be 20/12/19.

Any ideas?

 Item Date Hours A 18/12/19 1 B 18/12/19 0 A 19/12/19 0 B 19/12/19 2 A 20/12/19 0 B 20/12/19 1
 A 23/12/19 0 B 23/12/19 0

1 ACCEPTED SOLUTION
Community Support

Hi @Anonymous ,

Please try the measure below and see if the result achieve your expectation.

``````Measure =
var a = CALCULATE(MAX('Table'[Date]),FILTER(ALLEXCEPT('Table','Table'[Item]),'Table'[Hours]<>0))
return
IF(MAX('Table'[Date])=a,MAX('Table'[Hours]),BLANK())``````

Result would be shown as below.

Best Regards,

Jay

Community Support Team _ Jay Wang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Community Support Team _ Jay
If this post helps, then please consider Accept it as the solution
to help the other members find it.
4 REPLIES 4
Community Support

Hi @Anonymous ,

Please try the measure below and see if the result achieve your expectation.

``````Measure =
var a = CALCULATE(MAX('Table'[Date]),FILTER(ALLEXCEPT('Table','Table'[Item]),'Table'[Hours]<>0))
return
IF(MAX('Table'[Date])=a,MAX('Table'[Hours]),BLANK())``````

Result would be shown as below.

Best Regards,

Jay

Community Support Team _ Jay Wang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Community Support Team _ Jay
If this post helps, then please consider Accept it as the solution
to help the other members find it.
Super User

@Anonymous add following measure to get the date

``````First Date =
CALCULATE(
FIRSTDATE ( hurs[Date] ),
KEEPFILTERS( hurs[Hours] = 0 )
)``````

Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤

Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

Anonymous
Not applicable

Thanks Parry2k - First date records the first 0 in the list, I'm after the item before the last 0 in the data set,

This seems to work though, so I'm pretty sure you put me on the right track!

CALCULATE(LASTDATE(BUDGET_DATA[Date]),KEEPFILTERS((BUDGET_DATA[Hours]<>0)))

Memorable Member

Hi @Anonymous ,
I think your original max function should work with an added filter context. Try this as a calculated column:

``CALCULATE(MAX([Date]), FILTER(BUDGET_DATA, [Item] = EARLIER([Item]) && [Hours] <> 0))``

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.