Forum Discussion

rima_ch's avatar
rima_ch
Frequent Visitor
4 years ago
Solved

Get max date from all the previous date

I am trying to get the maximum of all the previous dates based on the selected date, I try to create a column but it returns null;  this is what I want to do: I have to add a new column to my table ...
Need Help
  • v-easonf-msft's avatar
    v-easonf-msft
    4 years ago

    Hi, rima_ch 

    You can try formula as below:

    Previous =
CALCULATE (
    MAX ( 'Table'[Date] ),
    FILTER ( ALL ( 'Table' ), 'Table'[Date] < EARLIER ( 'Table'[Date] ) )
)

    If it can't meet your requirement, please share you sample file for further researh.

     

    Best Regards,
    Community Support Team _ Eason

samdthompson's avatar
samdthompson
Icon for Memorable Member rankMemorable Member
4 years ago

Hello, my approach would be to do this is power query.

 

1. Create a table with Date,Previous in power query and sort it by Date <Descending>. Getting the dates will really depend heavily on what your underlying data looks like. The end result will look like the example you have above.

1. Make two duplicates of the new table in power query. 

2. to each of the new tables add an index column starting at 0 on the first and then at 1 on the second.

3. Merge the two queries together using the index column as the join key (full outer join)

4. expand out the date from the second table of the join.

5. Add a custom column to the new table with logic to account for the result you need in the earliest date

 

depending on your uses, these tables can be set to load or not within powerbi or used to merge into other tables