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 everyone,
I am writing to you about a measurement query that I can't solve.
Below is a table of yield calculation.
I would like to be able to display in the column "Rend_N-1(%)" the last value filled in the column "Rend (%)".
Example for the 21/12/2019 I would like "Rend_N-1 (%)" to be equal to 77.90% which corresponds to the "Rend (%)" of 03/12/2019.
Second example : For the 03/12/2019 I would like "Yield_N-1 (%)" to be equal to 76.72% which corresponds to the "Yield (%)" of the 12/11/2019.
I thought of using the "LASTNONBLANK" function but I still can't do it...
I also attach a PBIX to understand better : https://1drv.ms/u/s!Ao1OrcTeY008gYVQjq14qaCDLHAb6w?e=Jk3p2l
Thank you in advance for your help,
Joël
Hello @Anonymous ,
According to your examples, you just need to find the previous value of "Rend (%)" column.
If you are using calculated columns, then the solution can be found in this forum:
https://community.powerbi.com/t5/Desktop/Get-previous-row-value-in-new-column/td-p/496182
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!
Hello @ERD
Thank you for your help first of all. And to answer your question: no "Rend (%) is not a calculated column, it is a measure. I consulted your link and unfortunately it does not answer my problem. Any other ideas ?
Thanks in advance,
Joël
@Anonymous ,
In this case here is an option for a measure:
Rend_N-1 (%) measure =
VAR currentDate = SELECTEDVALUE(YourTableName[Date])
VAR previousDate = CALCULATE(
MAX(YourTableName[Date]),
FILTER(
ALLSELECTED(YourTableName),
YourTableName[Date] < currentDate)
)
RETURN
CALCULATE(
MAX(YourTableName[Rend (%)]),
FILTER(
ALLSELECTED(YourTableName),
YourTableName[Date] = previousDate
)
)
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!
Thank you for your help @ERD it is very generous of you. I just tried your formula but I don't understand very well in your formula or is it that I fill "Rend (%)" with a "MAX" function since "Rend %" is a measure and not a column and "Max" only works with column names it seems to me. I used the same code but with the "MAXX" function which can work with expressions but I have this error message that came up "A "FILTER" function was used in a True/False expression used as a table filter expression. This is not allowed." so no result either...
Did you try running your formula in the PBIX I made available?
Thanks in advance,
Joel
@Anonymous ,
I'm afraid I cannot download files, but to make MAXX work you can play with your current measure and try to use it in the MAXX function in the next way:
Rend_N-1 (%) measure =
VAR currentDate = SELECTEDVALUE(YourTableName[Date])
VAR previousDate = CALCULATE(
MAX(YourTableName[Date]),
FILTER(
ALLSELECTED(YourTableName),
YourTableName[Date] < currentDate)
)
RETURN
MAXX(
FILTER(
ALLSELECTED(YourTableName),
YourTableName[Date] = previousDate
),
[your_measure]
)
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!
Thanks @ERD but I have the same error message : "A "FILTER" function was used in a True/False expression used as a table filter expression. This is not allowed." when I used the "filter" function ...
@Anonymous,
Just in case, please, update your PBI Desktop.
Rend_N-1 (%) measure =
VAR currentDate = SELECTEDVALUE(dim_date[Date])
VAR previousDate = CALCULATE(
MAX(dim_date[Date]),
FILTER(
ALLSELECTED(dim_date),
dim_date[Date] < currentDate)
)
RETURN
MAXX(
FILTER(
ALLSELECTED(dim_date),
dim_date[Date] = previousDate
),
[Rend_dynamique (%)]
)
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!
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 |
---|---|
47 | |
24 | |
20 | |
15 | |
13 |
User | Count |
---|---|
51 | |
46 | |
39 | |
19 | |
19 |