Forum Discussion
Lookupvalue error same table working for some columns not all
Hello all,
My primary goal is to calculate the difference of a value between Period P and Period P-1. However, in the visualisation report, I have put a slicer to select only Period P. Therefore, the measure calculating Period P-1 does not appear due to the slicer. Therefore, I have decided to include a calculated column with the value of Period P-1 related to Period P.
To do so, I have created 2 keys, one with Period P and the other with Period P-1. The keys are related to the other columns (directly made in Power Query).
When I create a calculated column with Lookupvalue by linking both keys and any columns being the result, it does work fine.
Based on this, I assume that the Lookupvalue works fine as it is able to find the row with the correct data and no multiple values exist (for the keys).
However, and this is the strangest thing I've seen, once I try to get [Valeur] as the result, it gives me an error.
I am clearly missing a point here but I don't which one. All the data are formatted in the same way (non decimal figure). However, it seems like PBI cannot find anymore the data.
Please let me know what I am missing as I clearly don't understand. Everything is in the same table.
Thanks in advance for your help.
D1
Regarding your error, it is often caused by duplicate values.
Does this work?
CALCULATE(SUM('Magnitude'[Valeur]);FILTER('Magnitude';'Magnitude'[Key1]=EARLIER('Magnitude'[Key2])))If the problem persists, please share some sample data or sample pbix so that we can do more testing.
3 Replies
D1n2e3 , based on what I got. Create a table with year, period
and these new column
year period = [Year] *100 +[period]
Period Rank = RANKX(all('Period'),'Period'[year period],,ASC,Dense)
then try measures like
This Period = CALCULATE(sum('order'[Qty]), FILTER(ALL('Period'),'Period'[Period Rank]=max('Period'[Period Rank])))
Last Period = CALCULATE(sum('order'[Qty]), FILTER(ALL('Period'),'Period'[Period Rank]=max('Period'[Period Rank])-1))Hello,
Thanks for your answer. I followed the steps (I liked a lot the Year period and Period Rank ideas). However, I don't have any value for the Last Period once in the matrix on the visualization:
I had the same issue originally but I thought that the slicer would only show the selected Period and therfore the Period - 1 would not appear.
Kind regards,
D1
Regarding your error, it is often caused by duplicate values.
Does this work?
CALCULATE(SUM('Magnitude'[Valeur]);FILTER('Magnitude';'Magnitude'[Key1]=EARLIER('Magnitude'[Key2])))If the problem persists, please share some sample data or sample pbix so that we can do more testing.
