Forum Discussion

Icon for Power Participant rank

Power Participant

3 years ago

Create calculated columns dispaying First & Last date from slicer selection

I have a table called "Location" similar to the below... Region Country City Building Date (Today) EMEA UK London LON01 01/09/2023 EMEA Spain Madr...

Icon for Power Participant rank

Power Participant

3 years ago

I can get columns showing the first and the last date of the Date Table with...

First Date = Min('Table: Date'[Date])

Last Date = MAX('Table: Date'[Date])

but these dates dont change if I select a month from the date slicer.

DOLEARY85
Resident Rockstar
3 years ago
Hi,

try changing the filter context of the measure to allselected e.g:

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

CALCULATE(MIN('Date'[Date]),ALLSELECTED('Date'[Date]))

If I answered your question, please mark my post as solution, Appreciate your Kudos 👍
- StuartSmith
  Power Participant
  3 years ago
  Thanks for replying. I was hoping to go down the "Column" route for "First" and "Last" as currently i have a working report, but its only using a few actual data columns and then about 25 measure columns and as you know, measure columns dont work well with charts, etc. And want to move the measures to columns.
  
  If I can just get 2 columns that contain the first and last date of the slicer selection, I can then move all the measures also across to columns and then charts. But date columns dont filter with the slicers (see top table).
  
  If having "First" and "Last" date as columns isnt possible, is there a way to create columns that look at the measures as if I create a "Networkdays" column to look at the date measures, it doesnt count the number of days.
  
  I have attacehd an example .pbix file. Date Columns
  
  Thanks in advance.