Forum Discussion
Normalize time series data to first non-zero
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
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
6 Replies
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,
LydiaI 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?