Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.

Reply
sridharpolina
Helper I
Helper I

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

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_dateFCflow_dateProduct Quantity (BBLS)Next day
4/6/2021EPD South Plant4/30/2021318604/7/2021
4/6/2021EPD South Plant4/29/2021318604/7/2021
4/6/2021EPD South Plant4/28/2021318604/7/2021
4/6/2021EPD South Plant4/5/2021804644/7/2021
4/6/2021EPD South Plant4/4/2021870384/7/2021
4/6/2021EPD South Plant4/3/2021900564/7/2021
4/6/2021EPD South Plant4/2/2021872444/7/2021
4/6/2021EPD South Plant4/1/2021757504/7/2021
4/6/2021EPD South Plant4/27/2021866244/7/2021
4/6/2021EPD South Plant4/26/2021866244/7/2021
4/6/2021EPD South Plant4/25/2021866244/7/2021
4/6/2021EPD South Plant4/24/2021866244/7/2021
4/6/2021EPD South Plant4/23/2021866244/7/2021
4/6/2021EPD South Plant4/22/2021866244/7/2021
4/6/2021EPD South Plant4/21/2021866244/7/2021
4/6/2021EPD South Plant4/20/2021866244/7/2021
4/6/2021EPD South Plant4/19/2021866244/7/2021
4/6/2021EPD South Plant4/18/2021866244/7/2021
4/6/2021EPD South Plant4/17/2021866244/7/2021
4/6/2021EPD South Plant4/16/2021866244/7/2021
4/6/2021EPD South Plant4/15/2021866244/7/2021
4/6/2021EPD South Plant4/14/2021866244/7/2021
4/6/2021EPD South Plant4/13/2021866244/7/2021
4/6/2021EPD South Plant4/12/2021866244/7/2021
4/6/2021EPD South Plant4/11/2021866244/7/2021
4/6/2021EPD South Plant4/10/2021866244/7/2021
4/6/2021EPD South Plant4/9/2021866244/7/2021
4/6/2021EPD South Plant4/8/2021866244/7/2021
4/6/2021EPD South Plant4/7/2021866244/7/2021
4/6/2021EPD South Plant4/6/2021866244/7/2021
1 ACCEPTED SOLUTION
v-alq-msft
Community Support
Community Support

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):

c1.png

 

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:

c2.png

 

Best Regards

Allan

 

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

View solution in original post

4 REPLIES 4
v-alq-msft
Community Support
Community Support

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):

c1.png

 

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:

c2.png

 

Best Regards

Allan

 

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

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
Helper I
Helper I

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
Super User
Super User

@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...

Full Power BI Video 20 Hours YouTube
Microsoft Fabric Series 60+ Videos YouTube
Microsoft Fabric Hindi End to End YouTube

Helpful resources

Announcements
Feb2025 Sticker Challenge

Join our Community Sticker Challenge 2025

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

Jan NL Carousel

Fabric Community Update - January 2025

Find out what's new and trending in the Fabric community.