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.
Hello all,
I need help to figure out a formula to calculate the previous date/price before the last date/price. Here below please find a matrix for explanantions. How can I apply a formula for the last 2 columes "The date before the last Start Date" & "Price before latest price"?
Customer | Item | Start Date | End Date | Price | Latest Start Date | Latest Price | The date before the Last Start Date | Price before latest Price |
A | Item1 | 01/01/2019 | 31/12/2019 | 5 € | ||||
A | Item1 | 01/01/2020 | 30/04/2021 | 6 € | 01/01/2020 | 6 € | ||
A | Item1 | 01/05/2021 | 31/12/2021 | 7 € | 01/05/2021 | 7 € | ||
B | Item1 | 01/06/2017 | 31/12/2020 | 5,50 € | 01/06/2017 | 5,50 € | ||
B | Item1 | 01/01/2021 | 31/12/2099 | 6,50 € | 01/01/2021 | 6,50 € | ||
C | Item2 | 01/05/2018 | 31/12/2018 | 3 € | ||||
C | Item2 | 01/01/2019 | 30/06/2021 | 4 € | 01/01/2019 | 4 € | ||
C | Item2 | 01/07/2021 | 31/12/2022 | 5 € | 01/04/2021 | 5 € |
Many thanks in advance for your help.
Best regards
Rachel
Solved! Go to Solution.
Hi, @EZV12
Try formulas as below:
The date before the Last Start Date =
VAR _date =
CALCULATE (
MAX ( 'Table'[Start Date] ),
FILTER (
ALL ( 'Table' ),
'Table'[Customer] = MAX ( 'Table'[Customer] )
&& 'Table'[Item] = MAX ( 'Table'[Item] )
)
)
VAR _datebefore =
CALCULATE (
MAX ( 'Table'[Start Date] ),
FILTER (
ALL ( 'Table' ),
'Table'[Customer] = MAX ( 'Table'[Customer] )
&& 'Table'[Item] = MAX ( 'Table'[Item] )
&& 'Table'[Start Date] < _date
)
)
RETURN
IF (
SELECTEDVALUE ( 'Table'[Start Date] ) = _datebefore,
_datebefore,
BLANK ()
)
Price before latest Price =
VAR _date =
CALCULATE (
MAX ( 'Table'[Start Date] ),
FILTER (
ALL ( 'Table' ),
'Table'[Customer] = MAX ( 'Table'[Customer] )
&& 'Table'[Item] = MAX ( 'Table'[Item] )
)
)
VAR _datebefore =
CALCULATE (
MAX ( 'Table'[Start Date] ),
FILTER (
ALL ( 'Table' ),
'Table'[Customer] = MAX ( 'Table'[Customer] )
&& 'Table'[Item] = MAX ( 'Table'[Item] )
&& 'Table'[Start Date] < _date
)
)
VAR _price =
CALCULATE (
MAX ( 'Table'[Price] ),
FILTER ( 'Table', 'Table'[Start Date] = _datebefore )
)
RETURN
IF ( SELECTEDVALUE ( 'Table'[Start Date] ) = _datebefore, _price, BLANK () )
Please check my sample file for more details.
Best Regards,
Community Support Team _ Eason
Hi, @EZV12
Try formulas as below:
The date before the Last Start Date =
VAR _date =
CALCULATE (
MAX ( 'Table'[Start Date] ),
FILTER (
ALL ( 'Table' ),
'Table'[Customer] = MAX ( 'Table'[Customer] )
&& 'Table'[Item] = MAX ( 'Table'[Item] )
)
)
VAR _datebefore =
CALCULATE (
MAX ( 'Table'[Start Date] ),
FILTER (
ALL ( 'Table' ),
'Table'[Customer] = MAX ( 'Table'[Customer] )
&& 'Table'[Item] = MAX ( 'Table'[Item] )
&& 'Table'[Start Date] < _date
)
)
RETURN
IF (
SELECTEDVALUE ( 'Table'[Start Date] ) = _datebefore,
_datebefore,
BLANK ()
)
Price before latest Price =
VAR _date =
CALCULATE (
MAX ( 'Table'[Start Date] ),
FILTER (
ALL ( 'Table' ),
'Table'[Customer] = MAX ( 'Table'[Customer] )
&& 'Table'[Item] = MAX ( 'Table'[Item] )
)
)
VAR _datebefore =
CALCULATE (
MAX ( 'Table'[Start Date] ),
FILTER (
ALL ( 'Table' ),
'Table'[Customer] = MAX ( 'Table'[Customer] )
&& 'Table'[Item] = MAX ( 'Table'[Item] )
&& 'Table'[Start Date] < _date
)
)
VAR _price =
CALCULATE (
MAX ( 'Table'[Price] ),
FILTER ( 'Table', 'Table'[Start Date] = _datebefore )
)
RETURN
IF ( SELECTEDVALUE ( 'Table'[Start Date] ) = _datebefore, _price, BLANK () )
Please check my sample file for more details.
Best Regards,
Community Support Team _ Eason
Hello @v-easonf-msft ,
Thank you very much for your help and sorry for the late reply as I thought nobody could help during several days. I found out the solution by myself as well.
Many thanks for your time.
Best regards
Rachel
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 |
---|---|
104 | |
96 | |
80 | |
67 | |
62 |
User | Count |
---|---|
137 | |
106 | |
104 | |
81 | |
63 |