Formatting Today's ending values as tomorrow's beginning values

[sridharpolina]

I am trying to format the values( day's end value) in my table to show up as the next day's beginning values. For example I am trying to show 04/06/2021 day's value as the ending value as well as the 04/07/2021 beginning value so I could get the delta of the day over day difference but couldn't. Attached is sample of the data. Any help is very much appreciated.

as_of_date	FC	flow_date	Product Quantity (BBLS)	Next day
4/6/2021	EPD South Plant	4/30/2021	31860	4/7/2021
4/6/2021	EPD South Plant	4/29/2021	31860	4/7/2021
4/6/2021	EPD South Plant	4/28/2021	31860	4/7/2021
4/6/2021	EPD South Plant	4/5/2021	80464	4/7/2021
4/6/2021	EPD South Plant	4/4/2021	87038	4/7/2021
4/6/2021	EPD South Plant	4/3/2021	90056	4/7/2021
4/6/2021	EPD South Plant	4/2/2021	87244	4/7/2021
4/6/2021	EPD South Plant	4/1/2021	75750	4/7/2021
4/6/2021	EPD South Plant	4/27/2021	86624	4/7/2021
4/6/2021	EPD South Plant	4/26/2021	86624	4/7/2021
4/6/2021	EPD South Plant	4/25/2021	86624	4/7/2021
4/6/2021	EPD South Plant	4/24/2021	86624	4/7/2021
4/6/2021	EPD South Plant	4/23/2021	86624	4/7/2021
4/6/2021	EPD South Plant	4/22/2021	86624	4/7/2021
4/6/2021	EPD South Plant	4/21/2021	86624	4/7/2021
4/6/2021	EPD South Plant	4/20/2021	86624	4/7/2021
4/6/2021	EPD South Plant	4/19/2021	86624	4/7/2021
4/6/2021	EPD South Plant	4/18/2021	86624	4/7/2021
4/6/2021	EPD South Plant	4/17/2021	86624	4/7/2021
4/6/2021	EPD South Plant	4/16/2021	86624	4/7/2021
4/6/2021	EPD South Plant	4/15/2021	86624	4/7/2021
4/6/2021	EPD South Plant	4/14/2021	86624	4/7/2021
4/6/2021	EPD South Plant	4/13/2021	86624	4/7/2021
4/6/2021	EPD South Plant	4/12/2021	86624	4/7/2021
4/6/2021	EPD South Plant	4/11/2021	86624	4/7/2021
4/6/2021	EPD South Plant	4/10/2021	86624	4/7/2021
4/6/2021	EPD South Plant	4/9/2021	86624	4/7/2021
4/6/2021	EPD South Plant	4/8/2021	86624	4/7/2021
4/6/2021	EPD South Plant	4/7/2021	86624	4/7/2021
4/6/2021	EPD South Plant	4/6/2021	86624	4/7/2021

[v-alq-msft · Accepted solution]

Hi, @sridharpolina 

Based on your description, i created data to reproduce your scenario. The pbix file is attached in the end.

Table(The index is created in Power Query):

You may create a measure as below.

Result = 
SUMX(
    SUMMARIZE(
        'Table',
        'Table'[Next Date],
        "Re",
        var index = MAX('Table'[Index])
        return
            CALCULATE(
                SUM('Table'[Quantity(BBLS)]),
                FILTER(
                    'Table',
                    [Index]=index
                )
            )
    ),
    [Re]
)

Result:

Best Regards

Allan

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[sridharpolina]

Hi, I have tried this solution but it didn't work with my report but we implemented a data maodel change in the dataset itself so it could capture the previous value instead of calculating the value with DAX. It worked for us as it was a simple delta for those two columns. Hopefully the above solution helps other users if they are facing a similar situation.

[sridharpolina]

Sorry for not making my question very clear. Let me try and explain what my issue is here.

As of Date            Quantity(BBLs)

04/06/2021           86000

04/07/2021           85000

04/08/2021           86500

I need another column that looks like this

Beginning/Next Date            Quantity(BBLs)

04/07/2021                             86000

04/08/2021                             85000

04/09/2021                             86500

So the Today' s ending value(04/06/2021 - 86000) is carried over to Next Date Beginning Value (04/07/2021 - 86000). Once this is calculated then I need to calculate the difference between the two values so I could get the day over day difference(delta) of the values.

[amitchandak]

@sridharpolina , Not very clear , usually we can get perious day value measure with help from date table liek

This Day = CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Date]=max('Date'[Date])))
Last Day = CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Date]=max('Date'[Date])-1))
Last Day = CALCULATE(sum('order'[Qty]), previousday('Date'[Date]))

More option and column way

Day Intelligence - Last day, last non continous day
https://medium.com/@amitchandak.1978/power-bi-day-intelligence-questions-time-intelligence-5-5-5c324...