Add a column value based on most recent date and ID

[prakritnepal]

Hi, 

I am trying to create a dynamic snapshot of our Pipeline from salesforce. THis would enable us to see what the pipeline was 3 months ago or a year ago and filter it our accordingly. The process I am trying to follow is:

Opportunity ID	Amount	Last modified date	Stage
1234abc	5000	8/15/2018	90
1234abc	5000	6/15/2018	80
1234abc	5000	5/15/2018	70
1234abc	3000	4/15/2018	60
5678cde	15000	7/29/2018	90
5678cde	15000	7/15/2018	80
5678cde	15000	7/8/2018	70
5678cde	12000	6/25/2018	60
5678cde	12000	6/20/2018	30

Have a slicer determine the latest date. So lets say the date we chose is 6/20/18, the total amount in pipeline would be 17,000(12,000+5,000). If we chose 4/30/18, the amount would be 3000. 

I am not sure how I can select the most recent date < max date among selected dates by unique opportunity ID and sum one value per opportunity ID

Any help would be appreciated. 

Thanks. 

[v-frfei-msft]

Hi @prakritnepal,

I made one sample for your reference. Please refer to the steps as below.

1. Enter the data you shared and inset an index column that sort by date in power query.

2. Create a dimtime table.

Dimtime = CALENDARAUTO()

3. create the measure as below the get thre result as we excepted.

cal =
VAR inde =
    CALCULATE (
        MAX ( Table1[Index] ),
        FILTER (
            Table1,
            Table1[Last modified date] <= SELECTEDVALUE ( Dimtime[Date] )
                && Table1[Last modified date] <= SELECTEDVALUE ( Dimtime[Date] )
        )
    )
RETURN
    CALCULATE (
        SUM ( Table1[Amount] ),
        FILTER ( Table1, Table1[Index] <= inde && Table1[Index] >= inde - 1 )
    )

For more details, please check the pbix as attached.

Regards,

Frank

[prakritnepal]

Thank you for this example but you have created a measure to filter per opportunity ID to extract a value individually. I cannot do that because there are hundreds of thousands of opportunity IDs in salesforce. 

The logic I think that works but I am not being able to create in BI is:

For each [opportunity ID],       ' we cannot define opportunity ID as they are dynamic and there are new entries every day

select max(last modified date) 'this date needs to be <= a given date

sum(Amount), filter(as per selected date)

We need some sort of a loop in opportunity ID to extract only one date and one amount per ID with date having an additional clause. 

I am also getting incorrect values on certain dates. when I select 8/5/18, cal is 30K. This is not correct as per the data set.