Calculated column based on slicer value

[Anonymous]

I have been searching for hours, but can't find the solution.

I wish to create a calculated column, based on a selected date in a slicer.

Most solutions use a MIN or MAX function (in my case so far I used this: 

Date_selected =

CALCULATE(MAX(Periode[Date]),ALLSELECTED(Periode)))

This gives the correct date on the report page, but when used in the calculation column I created in the data tab, it goes to the last date in the Periode[Date] table I created.

Selected period is 2022-02 (Date is 28-02-2022), Period table runs to 2024-12 (31-12-2024)

I need the proper result to calculate number of months, which I do with:

#mnd =

DATEDIFF('KA tabel'[Startdatum], [Date_selected], MONTH)

Does anybody know how to fix this?

[neskowin]

I am trying to do something similar in terms of adding a calculated column to a data table. I'd like the column to be calculated using all the filters active on the page. I understand that if I change the filter setting, it wont' affect the calculated column until I refresh the page. However, it would allow me to "freeze" a computation based on current filter settings, and use the frozen column in other measures on the page. It would be ideal if the calculate columns could also be built using slicer selections on the page. Thanks

[v-yalanwu-msft · Accepted solution]

Hi, @Anonymous ;

Slicers cannot be used in calculated columns.  They can, however, be used in measures.

Slicers are Filters, and thus use filter context.  Filters can change dynamically based on what is selected, and how filter interactions are defined.  Since measures are dynamic fields calculated on demand, they can leverage this filter context to shape results.

Then we get to calculated columns.  Calculated columns use row context.  They are populated once at data refresh, and don't update again.  If you change interactions, change slicers, the underlying row context and any calculations performed at load would be unaffected.

So, you should use a measure instead of the calculated column if you would like to change the calculation in a table when the slicer value changed. as follows:

1.create another Date table.

Date = VALUES('Periode'[Date])

2.create a measure.

Date_selected = 
CALCULATE(MAX('Date'[Date]),ALLSELECTED('Date'))

#mnd = DATEDIFF(MAX([Startdatum]),[Date_selected],MONTH)

The final output is shown below:

Best Regards,
Community Support Team _ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

I understand that, but what measure, as the MAX function returns the incorrect value?

[johnt75]

If you have a slicer applied to the Periode table then MAX(Periode[Date]) should do it.

[Anonymous]

No, it doesn't.

As stated in the original question:

Period selected with the slicer is 2022-02 (Feb 28th 2022)

Period table runs to 2024-12 (Dec 31st 2024)

With the Max function, I get the 2024-12 Date returned, not the 2022-02 I selected with the slicer.

[johnt75]

Put your max date measure on a card visual and then use Performance Analyzer to get the DAX code which is generated for the card. See which filters are being applied to the measure and that may help you work out why you're getting an unexpected result.

[johnt75]

You can't make a calculated column be affected by slicers, calculated columns are only calculated during data load and refresh. If you need to take account of slicers and filters you need to use a measure.