Time Series

[MintuBaruah]

Hello All,

I have the data as shown in Table 1: 

Table1:

Time		Date		Balance
07:03:00 PM		4/27/2021		30142.78
06:28:00 PM		4/27/2021		33710.19
08:50:00 PM		4/14/2021		51329.08
05:44:00 PM		4/6/2021		79966.12
10:00:00 PM		5/28/2021		94621.27
05:15:00 PM		5/27/2021		94646.27
05:15:00 PM		5/27/2021		94681.27
01:35:00 PM		5/27/2021		94861.27
01:35:00 PM		5/27/2021		94896.27
03:41:00 PM		5/25/2021		101616.27
10:03:00 PM		5/18/2021		1616.27

The output should show the columns as Bal-start of the day and Bal-end of the day.

Output Table: 

Date		Bal-Start of day		Bal-End of day
4/27/2021		33710.19		30142.78
4/14/2021		51329.08		51329.08
4/6/2021		79966.12		79966.12
5/28/2021		94621.27		94621.27
5/27/2021		94861.27		94646.27
5/25/2021		101616.27		101616.27
5/18/2021		1616.27		1616.27

Please help resolve this query.

Thank you

[amitchandak · Accepted solution]

@MintuBaruah , Try two measures like

Bal-Start of day= firstnonblankvalue(Table[time], min(Table[Balance]))

Bal-End of day = lastnonblankvalue(Table[time], min(Table[Balance]))

[v-yalanwu-msft · Accepted solution]

Hi， @MintuBaruah ；

You also could create a measure as follows:

Start =
VAR _min =CALCULATE ( MIN ( [Time] ), ALLEXCEPT ( 'Table', 'Table'[Date] ) )
RETURN
    CALCULATE (MIN ( [Balance] ), FILTER ( ALLEXCEPT ( 'Table', 'Table'[Date] ), [Time] = _min ))

End =
VAR _max =CALCULATE ( MAX ( [Time] ), ALLEXCEPT ( 'Table', 'Table'[Date] ) )
RETURN
    CALCULATE (
        MIN ( [Balance] ),
        FILTER ( ALLEXCEPT ( 'Table', 'Table'[Date] ), [Time] = _max ))

The final output is shown below:

Best Regards,
Community Support Team_ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[amitchandak · Accepted solution]

@MintuBaruah , Try two measures like

Bal-Start of day= firstnonblankvalue(Table[time], min(Table[Balance]))

Bal-End of day = lastnonblankvalue(Table[time], min(Table[Balance]))