Problem in selecting Max date

[Anonymous]

Hello,

Can anyone guide me into the right direction here?

I need to add a column to a table, that returns the maximum date value from the table, considering also the current filter selection.

It works perfectly well, when I add it as a measure, but I cannot seem to figure a way to return this value into the table behind to be used in the creation of another custom column.

The setup is pretty basic. A fact table joined to a Dimensional date table.

Thanks in advance for any advice.

[themistoklis]

@Anonymous

Try the following DAX formula:

Column = CALCULATE(MAX('Calendar'[Date]), ALL('Master Table'))

Master Table is my fact table and Calendar is the custome calendar table.

You can change them based on your needs

[Anonymous]

Hi @themistoklis

Thanks for the reply.

Unfortunately, this returns the max of the Calendar date table, without taking the active filtering into consideration.

[themistoklis]

@Anonymous

When you say active filtering do you mean filter on dates or on other fields?

Can you also share the file with us, and a sample of the desired output?

[Anonymous]

@themistoklis

The filter is done by a slicer currently :).

I have created an example file. I'd like the Custom Column (Column_MaxDate) to return the same value as the Measure (Measure_MaxDate) does.

You can find the example file in the link below. If prompted a password use 'PBI'

https://transfernow.net/280sp1x9l54t

Again thanks for helping 🙂

[v-lili6-msft · Accepted solution]

HI, @Anonymous

calculated column and calculate table can't be affected by any slicer. you could create a measure instead of column.

Notice:

1. Calculation column/table not support dynamic changed based on filter or slicer.

(So MAX(table[column]) will return fixed value)
2. Measure can be affected by filter/slicer, so you can use it to get dynamic summary result.

here is reference:

https://community.powerbi.com/t5/Desktop/Different-between-calculated-column-and-measure-Using-SUM/t...

https://www.sqlbi.com/articles/calculated-columns-and-measures-in-dax/

Best Regards,
Lin

[Anonymous]

Hi  @v-lili6-msft

Thank you for the response and clarification - I appreciate that.

Best,

Steffen