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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
sahal_BI
New Member

How do i get the difference between two fruits from the previous day, see image below:

 

sahal_BI_1-1632410150804.png

 I need to find the difference in quantities for each individual fruits with respect to the previous value. I have previously tried using in built Power BI functions like "PreviousDay()" but haven't found success yet.

7 REPLIES 7
v-easonf-msft
Community Support
Community Support

Hi,  @sahal_BI 

You can also try calculated columns as below:

Previous Quantity =
VAR previoudate =
    CALCULATE (
        MAX ( 'Table'[Date] ),
        FILTER (
            'Table',
            'Table'[Fruit] = EARLIER ( 'Table'[Fruit] )
                && 'Table'[Date] < EARLIER ( 'Table'[Date] )
        )
    )
RETURN
    CALCULATE (
        MAX ( 'Table'[Quantity] ),
        FILTER (
            'Table',
            'Table'[Fruit] = EARLIER ( 'Table'[Fruit] )
                && 'Table'[Date] = previoudate
        )
    )
Difference = 'Table'[Quantity]-'Table'[Previous Quantity] 

45.png

Best Regards,
Community Support Team _ Eason
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

 

ERD
Super User
Super User

@sahal_BI ,

You can try this measure (please, provide sample data in a table format next time):

Prev_qty = 
VAR currentFruit = SELECTEDVALUE ( 'Table'[Fruit] )
VAR currentDate = SELECTEDVALUE ( 'Table'[Date] )
VAR prevDate =
    CALCULATE (
        MAX ( 'Table'[Date] ),
        FILTER (ALL ( 'Table' ), 'Table'[Date] < currentDate && 'Table'[Fruit] = currentFruit)
    )
RETURN
    COALESCE (
        CALCULATE (
            SUM( 'Table'[Qty] ),
            FILTER (ALL ( 'Table' ), 'Table'[Date] = prevDate && 'Table'[Fruit] = currentFruit)
        ),
        0
    )

ERD_0-1632425995962.png

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

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

Check out my latest demo report in the data story gallery.

Stand with Ukraine!


Here are official ways you can support Ukraine financially (accounts with multiple currencies):
1) Support the Armed Forces of Ukraine: https://bank.gov.ua/ua/about/support-the-armed-forces
2) Come Back Alive foundation: https://www.comebackalive.in.ua/

Thank you!

Caz_16
Helper II
Helper II

Use a Variable to subtract one day before executing your measure. Im assuming you have a Calendar Table in your model. If not, use your Date column everywhere you see "CalendarTable[Date]". You should also consider a calendar table for doing time series calculations. 

 

Previous Day = 
VAR DayBefore = MAX(Calendar[Date]) - 1
VAR MaxDate = MAX(Calendar[Date])
RETURN
CALCULATE(
    SUM(Quantity),
    ALLSELECTED(Fruit),
    CalednarTable[Date] = DayBefore
)
-
CALCULATE(
    SUM(Quantity),
    ALLSELECTED(Fruit),
    CalednarTable[Date] = MaxDate
)

 

Hope this helps. 

 

-Caz

Didn't work

@sahal_BI 

TL;DR - Please don't post replies like "Didn't work". Do your research before posting. Provide feedback to those taking time to help you. 

 

Well, unfortunately just saying that it "Didn't work" doesn't really do me any good in providing you further insight.

 

I will cut you some slack, seeing as you are a new member, and I strongly suggest you read over this post before posting your questions on the forum. https://community.powerbi.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/td-p/1447523/jump...

I would venture to guess that your question has been answered before. So go try and find it in the forum before posting a question. 

 

You also need to recognise that solutions that are provided anywhere on this forum are not going to be "Copy and Paste". You are likely going to need to modify the DAX to work in your code (We don't know your columns, measures, relationships, and so on). Also consider that the code provided may be the building blocks of the solution, or the piece of the puzzle you were previously missing. If you don't understand a function in the code someone gives you, Google it. Learn something new. It very well may be something that you can adapt to your need and solve your solution. and at the very least you can learn something that you can apply to your work down the road. 

 

Now, would you like to elaborate on what about the DAX that we provided didn't work? Did it not provide you with the result you expect? Did it error out? Please elaborate. 

 

-Caz

amitchandak
Super User
Super User

@sahal_BI , Try a new column

 

new column =
var _max = maxx(filter(Table, [fruit] = earlier([Fruit]) && [Date] <earlier([Date]) ), [Date] )
return
[Price]- maxx(filter(Table, [fruit] = earlier([Fruit]) && [Date] =_max ), [price] )

Didn't work unfortunetaly

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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