Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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.
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]
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.
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
)
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!
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
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
@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
User | Count |
---|---|
122 | |
109 | |
96 | |
59 | |
57 |
User | Count |
---|---|
143 | |
119 | |
102 | |
71 | |
61 |