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

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

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
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

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

MayPowerBICarousel

Power BI Monthly Update - May 2024

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