Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi,
I am new to Power BI. I want to calculate the previous duration record of a project. I have being trying with Earlier function. But that did not work for me. Please dont use previousYear-fucntion because the dates vary alot. this is just a simple example
Can anyone please help me?
Regards,
Elouizi
Solved! Go to Solution.
Hi,
This calculated column works fine
=LOOKUPVALUE('Duration'[Duration],'Duration'[Date],CALCULATE(MAX('Duration'[Date]),FILTER('Duration','Duration'[Brand]=EARLIER('Duration'[Brand])&&'Duration'[Date]<EARLIER('Duration'[Date]))),'Duration'[Brand],'Duration'[Brand])
Hope this helps.
Hi,
This calculated column formula works
=LOOKUPVALUE(Data[DurationinMinutes],Data[Date],CALCULATE(MAX(Data[Date]),FILTER(Data,Data[Brand]=EARLIER(Data[Brand])&&Data[Date]<EARLIER(Data[Date]))),Data[Brand],Data[Brand])
Hope this helps.
Hi Ashish_Mathur,
My replay was to you. :).
I dont know why, but it says that it cannot find the column "brand" and the "date" columns.
Is there another way I can solve this without creating a colum? Otherwise I may need to create lots of other columns
Thanks!
Hi,
Share the link from where i can download your PBI file.
Hi Ashish,
Please find the links below:
data file (Excel and pbix file)
https://mega.nz/file/9aYRBIRa#IO2OJ9T9xOo63BGMMjbKBxPGYZ78xwMNkhsPboBdBAY
https://mega.nz/file/lOJnQSaZ#GKScuWtFwcFh6-N-AhBz-mnP9buWpi60LHH4RstEVMQ
Many Thanks!
Hi,
This calculated column works fine
=LOOKUPVALUE('Duration'[Duration],'Duration'[Date],CALCULATE(MAX('Duration'[Date]),FILTER('Duration','Duration'[Brand]=EARLIER('Duration'[Brand])&&'Duration'[Date]<EARLIER('Duration'[Date]))),'Duration'[Brand],'Duration'[Brand])
Hope this helps.
Looks amazing. Is there a way I can use it as a messaure? Because I need to do other calculations, otherwise I need to create a lot of columns (which will impact the performance)
Whom are you replying to? Did my calculated column formula work?
@elouizi - If you want to create a column, you can use EARLIER, see my article on Mean Time Between Failure (MTBF) which uses EARLIER: http://community.powerbi.com/t5/Community-Blog/Mean-Time-Between-Failure-MTBF-and-Power-BI/ba-p/3395....
The basic pattern is:
Column =
VAR __Current = [Value]
VAR __Previous = MAXX(FILTER('Table','Table'[Date] < EARLIER('Table'[Date])),[Value])
RETURN
__Current - __Previous
If you want a measure then @mahoneypat 's formula should work.
Are you trying to create a new column or measure? Here is a measure expression that should work. Replace "Table" with your actual table name and try it in a table visual with your Date, Brand, and Duration columns.
LastDuration =
VAR thisdate =
MIN ( Table[Date] )
RETURN
CALCULATE (
LASTNONBLANKVALUE ( Table[Date], MAX ( [Table[Duration] ) ),
ALLEXCEPT ( Table, Table[Brand] ),
Table[Date] < thisdate
)
If this works for you, please mark it as the solution. Kudos are appreciated too. Please let me know if not.
Regards,
Pat
To learn more about Power BI, follow me on Twitter or subscribe on YouTube.
Hi,
Thank you so much for you quick response.
I tried the formula. But it showed me the previous dates and not the previous duration.
I changed the formula a little bit to get the duration out of it. But it did not give me the expected results.
Could you please explain the formula? I'm a newbie that wants to learn new things.
Thanks!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
114 | |
99 | |
83 | |
70 | |
60 |
User | Count |
---|---|
150 | |
115 | |
104 | |
89 | |
65 |