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]

Some more background for those of you reading this. My goal is to identify the date a reservoir was last 98% full.

My larger daily data set contains several columns including:
1) Daily Date
2) Year
3) Reservoir contents
4) Percent full

I've created a measure to determine the minimum reservoir contents for the period, and a second measure to lookup the date value that the minimum occurs on. Now I'm trying to find the date before the minimum date when the reservoir was last 98% full.

So for example, the minimum contents may occur on 3/21/2003, and during 2002, the reservoir only reached a 70% full, but in 2001 on June 4, 2001 the reservoir was above 98% full.

The end goal is to create a measure to get the 6/4/2001 date value.

The values column above is something I created with if statements to identify the rows where the maximum reservoir percent full for each year is over 98%. I was thinking if I could calculate the count column, I could subtract that from the minimum contents date to get the date I want.

But of course I'm open to other suggestions.

 

[RoryA]

This sounds like a very roundabout way to go. Just create a measure that filters your table for dates less than the minimum's date where the value was >=0.98 and then calculate the max date from that filtered table.

 

[hayden]

@RoryA, Thank you for helping me step away from the trees to look at the forest, and find a simpler way to do this. I think your approach will work much better than mine.

I think my measure needs to be something like this:

StartDate:=CALCULATE(max(DailyTable[Date]),FILTER(DailyTable,DailyTable[Date]<[CriticalEndDate]) && FILTER(DailyTable,DailyTable[AnnualMaxPerFull]>=DailyTable[PerResCritical]))

Where:
[CriticalEndDate] is a measure looking up the minimum reservoir contents date (i.e. 3/21/2003).
[AnnualMaxPerFull] are the percent full values by date
[CriticalEndDate] is a column of values representing the 98% which I pull in from a table variable.

But that gives me an error.
Semantic Error: A function 'FILTER' has been used in a True/False expression that is used as a table filter expression. This is not allowed:

Can you help me with the format?

And just to help visualize here is some dummy data. Were trying to get the values highlighted green.

 

[hayden]

Okay, still stuck on trying to calculate the maximum date given two criteria as @RoryA described above.

Now thinking its something like this:

CriticalStartDate:=Maxx(FILTER(DailyTable,DailyTable[PerCap8Res]>=TblUserInput[CriticalFullPercentage] && DailyTable[Date]<=[CriticalEndDate]),[Date])

Where
DailyTable contains my dates.
[PerCap8Res] is the Reservoir percentage full from 0 to 100% (i.e. the 3rd column above).
TblUserInput[CriticalFullPercentage] is the 98% reservoir contents. It is a measure from a separate table.
[Date] are the dates I'm trying to filter for dates earlier than the end of the critical period (i.e. the first column above).
[CriticalEndDate] is a measure on my DailyTable that returns the data given the minimum reservoir contents using Lookupvalue (i.e. the blue row above).

Any help on this would be great!

 

[RoryA]

It's very hard (for me at least) to answer this out of context.
First question: what is happening if you enter the measure you just posted?

 

[hayden]

Sorry Rory.

The result of the measure I just posted is just a blank:

 

[RoryA]

That would suggest to me that nothing is matching your criteria.

Any chance you can post a sample file somewhere with some anonymous data?

 

[hayden]

Here is some sample data:

12-11-23 SampleData.xlsx

Shared with Dropbox

www.dropbox.com

 

[RoryA]

Change the query so that it returns PercentFull as a Decimal number (it's being loaded to the model as text at the moment), create a measure called UserPct for the percentage threshold using:

=MAX(UI_PercentContents[UI_PercentContents])

and then amend the date measure to use:

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