Calculated column based on formula, filter, month and year - using slicer

[midrississi]

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) :

NEW_PRICE =

CALCULATE(MAX('Table'[PRICE]),

FILTER('Table',EARLIER('Table'[NO_YEAR])='Table'[NO_YEAR] &&

EARLIER('Table'[ID_ITEM])='Table'[ID_ITEM] &&

'Table'[NO_MONTH]=MAXX(FILTER('Table',EARLIER('Table'[NO_YEAR])='Table'[NO_YEAR]),'Table'[NO_MONTH])

)

)

It works, however, when I use the slicer for the visualization, the date slicer doesn't have any influence in my new column and it still returns the value of the whole table.

for exemple, if my slicer indicate values between 01/01/2018 and 01/10/2019, my new column show always the new price of the last month (01/12/2019) which is 19 instead of taking the PRICE of the new last month which is 23.

 

Is there anything I can do about that? Any formula that cooperates with the date and will return a value only for the time period I filter using the slicer?

Thanks in advance

[AlB]

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 

[midrississi]

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,

[AlB · Accepted solution]

@midrississi 

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 

[midrississi]

@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,

[AlB]

@midrississi 

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 

[midrississi]

@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,

[midrississi]

@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

[amitchandak]

@midrississi , You can not use slicer value in a new column.

[midrississi]

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