Forum Discussion

StuartSmith's avatar
StuartSmith
Icon for Power Participant rankPower 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 Madrid MAD01 01/09/2023
EMEA France Paris PAR01 01/09/2023
EMEA Germany Berlin BER01 01/09/2023

 

I also have a "Date" table and a "Slicer" with the "Date" column from the "Date" table.  

 

I currently have too many measures and finding it hard with chart visuals, etc. and therefore trying to create a new table using calculated columns instead of measures.  Is it possible to create 2 calculated columns called "FirstDate" & "LastDate" that will display the first and last date of the slicer selection?  So if the Slicer is "January", First date is 01st Jan and Last date is 31st Jan.  To create a relationship between the tables, I created a "Date" column that uses "Todays" date.

 

Region Country City Building Date (Today) First Date Last Date
EMEA UK London LON01 01/09/2023 01/01/2023 31/01/2023
EMEA Spain Madrid MAD01 01/09/2023 01/01/2023 31/01/2023
EMEA France Paris PAR01 01/09/2023 01/01/2023 31/01/2023
EMEA Germany Berlin BER01 01/09/2023 01/01/2023 31/01/2023

 

Thanks in advance.

 

Need Help

3 Replies

  • StuartSmith's avatar
    StuartSmith
    Icon for Power Participant rankPower 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's avatar
      DOLEARY85
      Icon for Resident Rockstar rankResident 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's avatar
        StuartSmith
        Icon for Power Participant rankPower 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.