Power Pivot - Count Until

[hayden]

I have a column of data created through prior if statements that contain either a value of zero, or a value greater than zero. I'm trying to create a new column that will count the days until the value is greater than 1. Below is an example of the data (Value column) and the result I'd like (count). I have a feeling this is a calculate with distinct count and some filter, but I can't seem to craft something that works.

In excel, I would simply start a formula in row 2 that was if(value>0,0,Count from row above + 1).

Values	Count
0	1
0	2
0	3
0	4
0	5
0.987	0
0	1
0	2
.99	0

 

[hayden]

Thank you Rory.

That returns the last date that had a contents of 98% or more. Is it possible to modify this to return the date that the maximum percent contents occurred during that year? (i.e. it currently returns 8/18/2001, the last date that had a contents of at least 98%, I'm hoping to return 6/4/2001, which is the date with the highest contents during 2001.

I wonder if I could create a group by table with the maximum dates, then filter by those dates? I've saved an updated version of the sample file here:

12-11-23 SampleData.xlsx

Shared with Dropbox

www.dropbox.com

 

[RoryA]

That returns the last date that had a contents of 98% or more

Well, that is what you asked for, so...

Is it simply the max value that occurred at any point during that year?

 

[hayden]

Thanks for the patience as I try and ask my question again.

We know the minimum reservoir contents for the entire period of record occurred on March 21, 2003.

I want to return the date that the reservoir contents was last at its maximum for the year, but only if the percent full associated with that maximum value is over 98%.

 

[hayden]

I think I got it!
Created a new table called FullPct that returns the Year and the maximum percentage full using groupby.

In my Daily Table:
Added a column for the Year [WaterYr] using Date.Year....
Added a column for PctFull with this:

=CALCULATE(FIRSTNONBLANK(FullPct[FullPct],1),FILTER(FullPct,FullPct[WaterYr]=Daily[WaterYr] && FullPct[FullPct]=Daily[PercentFull]))

This returns the maximum percentage full by year on the date it occurred, otherwise its blank.

Modified the measure that Rory helped with to:

StartDate:=VAR startFrom = [EndDate] RETURN maxx(FILTER(Daily,Daily[PctFull]>=[UserPct] && Daily[Date]<=startFrom),Daily[Date])

@RoryA - Thanks for your patience and help.

 

[hayden]

Well, when I moved out of my sample data and into my main data it didn't work...

Here is what I finally settled on.
Create a query which returns a table of year and max percentage each year using groupby.
Merge this query with the daily query based on Year and Percentage (this is the multiple criteria query).
Expand the resulting table to just keep the date. Now I have a table with the year, max percentage, and date it occurred.

Finally, write a measure for this new table that calculates the max with filter and the && symbols.

CriticalStartDate:=CALCULATE(MAX(MaxPct[DailyTable.Date]),FILTER(MaxPct,MaxPct[DailyTable.Date]<=DailyTable[CriticalEndDate] && MaxPct[MaxPct]>=TblUserInput[CriticalFullPercentage]))