cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
sridharpolina
Frequent Visitor

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.

View solution in original post

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
Frequent Visitor

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

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



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS Attendee Badge

Claim Your Badge & Digital Swag!

Check out how to claim yours today!

secondImage

Are You Ready?

Test your skills now with the Cloud Skills Challenge.

Top Solution Authors