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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

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

Helpful resources

Announcements
Fabcon_Europe_Social_Bogo

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.