Normalize time series data to first non-zero

[Anonymous]

I have a time-series table that has daily date resolution. The table has unique properties that have individual arrays with varying first production dates. What I want to be able to do is a build a visualization that aggregates common properties but summarizes the production arrays to Day 0 equaliing the First Non-Zero value.

For example, if Property A has its first non-zero value on 2/15 and equals 10 units while Property B has its first non-zero value on 3/1 and equals 20 units

Then I want the visualization to aggregate Day 0 to Day 365 and Day 0 would equal 30 units in this case. 

Thanks for the help

[v-yuezhe-msft]

@Anonymous,

I make a test in my sample table.



Firstly, create a new table using DAX below.

NewTable = CALCULATETABLE(TEST,TEST[unit]>0)

Secondly, create the following columns in the new table.

Index = RANKX(FILTER(NewTable,NewTable[property]=EARLIER(NewTable[property])),[Date],,ASC,Dense)

Day = "Day" &(NewTable[Index]-1)

At last, create chart as below. If the above DAX don't help, please share sample data of your table for us to analyze.



Regards,
Lydia

[Anonymous]

I have a follow up question. It is not unusual that some of the data arrays will have zero values following the first non-zero value. I'd like to include these in the evaluation, so in this case, we are only filtering out all zeros before the first non-zero. 

How could I modify the calculatetable formula in that regard?

[v-yuezhe-msft]

@Anonymous,

Could you please share sample data of your table here?


Regards,
Lydia

[Anonymous]

@v-yuezhe-msft

ID	Date	Production
A	1/1/2019	0
A	1/2/2019	0
A	1/3/2019	0
A	1/4/2019	0
A	1/5/2019	0
A	1/6/2019	0
A	1/7/2019	0
A	1/8/2019	0
A	1/9/2019	10
A	1/10/2019	20
A	1/11/2019	30
A	1/12/2019	40
A	1/13/2019	50
A	1/14/2019	50
A	1/15/2019	50
A	1/16/2019	0
A	1/17/2019	0
A	1/18/2019	0
A	1/19/2019	0
A	1/20/2019	0
A	1/21/2019	0
A	1/22/2019	10
A	1/23/2019	20
A	1/24/2019	30
A	1/25/2019	40
A	1/26/2019	50
A	1/27/2019	50
A	1/28/2019	50
A	1/29/2019	50
A	1/30/2019	50
A	1/31/2019	50

[v-yuezhe-msft · Accepted solution]

@Anonymous,

Please create the following measure and check column in your original table.

Measure = MAXX(Table1,CALCULATE(MIN(Table[Date]),FILTER(ALL(Table), Table[ID]= EARLIER(Table[ID])&&Table[Production]>0)))

Check = IF([Measure]<=Table[Date],1,0)

Then create new table using DAX below.

NewTable = CALCULATETABLE(Table,Table[Check]>0)

And change DAX of Index column to the following:

Index = RANKX(FILTER(NewTable,NewTable[ID]=EARLIER(NewTable[ID])),[Date],,ASC,Dense)

Regards,
Lydia

[Anonymous]

Thank you. That was perfect. Appreciate the help