Get max date from all the previous date

[rima_ch]

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 "Previous"(which is the maximum date for all the dates that are< to date "for that row"

thank you in advance 

[v-easonf-msft · Accepted solution]

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]

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

[AlexisOlson]

@rima_ch You may be interested in these prior posts as they both use the indexing method suggested by @samdthompson.

https://community.powerbi.com/t5/Desktop/Calculating-Daily-values-from-Cumulative-Total/m-p/2198969

https://community.powerbi.com/t5/Power-Query/Optimize-performance-at-Un-Cumulate-calculation/m-p/219...

[smpa01]

@rima_ch  provide sample data, hard to guess otherwise.