Frequent Visitor

5 years ago

Solved

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

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

Calculated columns

DAX

Need Help DAX

Slicer Visualizations

AlB

5 years ago

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

9 Replies

amitchandak
Super User
5 years ago
midrississi , You can not use slicer value in a new column.
- midrississi
  Frequent Visitor
  5 years ago
  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

AlB

Community Champion

5 years ago

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

Frequent Visitor

5 years ago

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

Community Champion

5 years ago

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

Forum Discussion

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

9 Replies

Related content

Calculating with Filters

If formula

DAX Formula Help

formula

Calculate Filters