Forum Discussion

MintuBaruah's avatar
MintuBaruah
Helper III
5 years ago
Solved

Time Series

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

Need Help
  • amitchandak's avatar
    amitchandak
    5 years ago

    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's avatar
    v-yalanwu-msft
    5 years ago

    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.

2 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    5 years ago

    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's avatar
    v-yalanwu-msft
    Community Support
    5 years ago

    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.