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'm looking for a formula display the column "previous close" in this table.
My table is linked to a proper functioning date table. A formula with "PREVIOUS" is not working because of weekends.
Thnx for helping me out.
Carla
Solved! Go to Solution.
Hi @Carla17
You can use the following two methods to add a new column.
Previous Close =
MAXX (
TOPN (
1,
FILTER (
'Table',
'Table'[Ticker] = EARLIER ( 'Table'[Ticker] )
&& 'Table'[Date] < EARLIER ( 'Table'[Date] )
),
'Table'[Date], DESC
),
'Table'[Close]
)
Or
Previous Close 2 =
VAR _previousDate =
MAXX (
FILTER (
ALL ( 'Date' ),
'Date'[Work Day] = 1
&& 'Date'[Date] < EARLIER ( 'Table'[Date] )
),
'Date'[Date]
)
VAR _previousClose =
MAXX (
FILTER (
'Table',
'Table'[Ticker] = EARLIER ( 'Table'[Ticker] )
&& 'Table'[Date] = _previousDate
),
'Table'[Close]
)
RETURN
_previousClose
With the second method, you need to have a [Work Day] column in Date table to mark if a date is a working day.
Regards,
Community Support Team _ Jing
If this post helps, please Accept it as the solution to help other members find it.
Hi @Carla17
You can use the following two methods to add a new column.
Previous Close =
MAXX (
TOPN (
1,
FILTER (
'Table',
'Table'[Ticker] = EARLIER ( 'Table'[Ticker] )
&& 'Table'[Date] < EARLIER ( 'Table'[Date] )
),
'Table'[Date], DESC
),
'Table'[Close]
)
Or
Previous Close 2 =
VAR _previousDate =
MAXX (
FILTER (
ALL ( 'Date' ),
'Date'[Work Day] = 1
&& 'Date'[Date] < EARLIER ( 'Table'[Date] )
),
'Date'[Date]
)
VAR _previousClose =
MAXX (
FILTER (
'Table',
'Table'[Ticker] = EARLIER ( 'Table'[Ticker] )
&& 'Table'[Date] = _previousDate
),
'Table'[Close]
)
RETURN
_previousClose
With the second method, you need to have a [Work Day] column in Date table to mark if a date is a working day.
Regards,
Community Support Team _ Jing
If this post helps, please Accept it as the solution to help other members find it.
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 |
---|---|
113 | |
100 | |
78 | |
76 | |
52 |
User | Count |
---|---|
146 | |
109 | |
106 | |
88 | |
61 |