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 everybody,
I'd like to ask you for help with a calculated column,
I'll use the following table to demostrate it on an example,
Table
In order to get the Requested column NEW_PRICE I use this formula (in this column I want to always have the PRICE of the last month of the year for all the other months) :
Solved! Go to Solution.
Try this. If it doesn't work I'd need the pbix to run some tests (or a pbix with dummy data that repros the issues)
New price =
VAR lastMonth_ =
CALCULATE ( MAX ( 'Table'[NO_MONTH] ),
ALLSELECTED(),
SUMMARIZE('Table','Table'[ID_ITEM],'Table'[NO_YEAR] )
)
VAR priceMoisAnnee_ =
CALCULATE (
MAX ('Table'[PRICE]),
'Table'[NO_MONTH] = lastMonth_,
ALLSELECTED(),
SUMMARIZE('Table','Table'[ID_ITEM],'Table'[NO_YEAR] )
)
RETURN
priceMoisAnnee_
Please mark the question solved when done and consider giving a thumbs up if posts are helpful.
Contact me privately for support with any larger-scale BI needs, tutoring, etc.
Cheers
Hi @midrississi
Please always show your sample data in text-tabular format in addition to (or instead o) the screen captures. A screen cap helps, like in this case, but doesn't allow people to readily copy the data and run a quick test and thus decreases the likelihood of your question being answered. Just use 'Copy table' in Power BI and paste it here.
Don't create a calculated column. That is static and once created will not change, certainly not under a slicer. Create a measure instead:
New price =
VAR lastDate_ =
CALCULATE ( MAX ( Table1[DT_Day] ), ALLEXCEPT ( Table1, Table1[ID_Item] ) )
VAR priceLastDate_ =
CALCULATE (
MAX ( Table1[Price] ),
Table1[DT_Day] = lastDate_,
ALLEXCEPT ( Table1, Table1[ID_Item] )
)
RETURN
priceLastDate_
Place that measure in a table visual just like you had it, with all those five fields in the table
Please mark the question solved when done and consider giving a thumbs up if posts are helpful.
Contact me privately for support with any larger-scale BI needs, tutoring, etc.
Cheers
Hi @AlB ,
Thank you for the help, I tried to apply your code in my case, it gives the true values, but the slicer doesn't have any effect in my new measure added to the table when I change the date.
this is the code I used based on your code, still don't have the effect of slicer on.
New price =
VAR lastMonth_ =
CALCULATE ( MAX ( 'Table'[NO_MONTH] ),
ALLSELECTED('Table'[DT_DAY]),
ALLEXCEPT ( 'Table','Table'[ID_ITEM],'Table'[NO_YEAR] )
)
VAR priceMoisAnnee_ =
CALCULATE (
MAX ('Table'[PRICE]),
'Table'[NO_MONTH] = lastMonth_,
ALLEXCEPT ( 'Table','Table'[ID_ITEM],'Table'[NO_YEAR] )
)
RETURN
priceMoisAnnee_
this is the result before applying any change on slicer (the result was good)
ID_ITEM | DT_DAY | NO_YEAR | NO_MONTH | PRICE | NEW_PRICE |
1 | 01/01/2018 | 2018 | 1 | 19 | 22 |
1 | 01/02/2018 | 2018 | 2 | 20 | 22 |
1 | 01/03/2018 | 2018 | 3 | 23 | 22 |
1 | 01/04/2018 | 2018 | 4 | 23 | 22 |
1 | 01/05/2018 | 2018 | 5 | 22 | 22 |
1 | 01/06/2018 | 2018 | 6 | 19 | 22 |
1 | 01/07/2018 | 2018 | 7 | 22 | 22 |
1 | 01/08/2018 | 2018 | 8 | 24 | 22 |
1 | 01/09/2018 | 2018 | 9 | 20 | 22 |
1 | 01/10/2018 | 2018 | 10 | 18 | 22 |
1 | 01/11/2018 | 2018 | 11 | 25 | 22 |
1 | 01/12/2018 | 2018 | 12 | 22 | 22 |
1 | 01/01/2019 | 2019 | 1 | 22 | 19 |
1 | 01/02/2019 | 2019 | 2 | 20 | 19 |
1 | 01/03/2019 | 2019 | 3 | 20 | 19 |
1 | 01/04/2019 | 2019 | 4 | 19 | 19 |
1 | 01/05/2019 | 2019 | 5 | 25 | 19 |
1 | 01/06/2019 | 2019 | 6 | 23 | 19 |
1 | 01/07/2019 | 2019 | 7 | 19 | 19 |
1 | 01/08/2019 | 2019 | 8 | 20 | 19 |
1 | 01/09/2019 | 2019 | 9 | 23 | 19 |
1 | 01/10/2019 | 2019 | 10 | 23 | 19 |
1 | 01/11/2019 | 2019 | 11 | 22 | 19 |
1 | 01/12/2019 | 2019 | 12 | 19 | 19 |
1 | 01/01/2020 | 2020 | 1 | 22 | 20 |
1 | 01/02/2020 | 2020 | 2 | 20 | 20 |
1 | 01/03/2020 | 2020 | 3 | 21 | 20 |
1 | 01/04/2020 | 2020 | 4 | 20 | 20 |
but after using slicer the result doesn't change. This is the screen of my result :
Thank you in advance.
Best Regards,
Try this. If it doesn't work I'd need the pbix to run some tests (or a pbix with dummy data that repros the issues)
New price =
VAR lastMonth_ =
CALCULATE ( MAX ( 'Table'[NO_MONTH] ),
ALLSELECTED(),
SUMMARIZE('Table','Table'[ID_ITEM],'Table'[NO_YEAR] )
)
VAR priceMoisAnnee_ =
CALCULATE (
MAX ('Table'[PRICE]),
'Table'[NO_MONTH] = lastMonth_,
ALLSELECTED(),
SUMMARIZE('Table','Table'[ID_ITEM],'Table'[NO_YEAR] )
)
RETURN
priceMoisAnnee_
Please mark the question solved when done and consider giving a thumbs up if posts are helpful.
Contact me privately for support with any larger-scale BI needs, tutoring, etc.
Cheers
@AlB ,
The same thing the code gives the good result before filter but after filter I don't have what I want.
How I can share with you the .pbix project ?
Thank you in advance.
Best regards,
You have to share here the URL to the file hosted elsewhere: Dropbox, Onedrive... or just upload the file to a site like tinyupload.com (no sign-up required).
Please mark the question solved when done and consider giving a thumbs up if posts are helpful.
Contact me privately for support with any larger-scale BI needs, tutoring, etc.
Cheers
@AlB ,
Thank you for finding the URL to download the .pbix project.
URL : http://s000.tinyupload.com/?file_id=00669915136941173932
Thank you in advance for your help.
Best regards,
@AlB
It's okay I found where the problem comes from.
Instead of using the "DT_DATE" field which is in the fact table to filter in the slicer, I used the one which is in the Date dimension table and it worked.
Thanks for your help.
Best regards
@midrississi , You can not use slicer value in a new column.
Hi @amitchandak,
thank you for your reply. But is there another solution using a calculated measure, for example, to meet this need?
thank you in advance for your return
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 | |
14 | |
12 |
User | Count |
---|---|
55 | |
48 | |
43 | |
19 | |
18 |