How to get Value entered on last date in the Dax Calculation

[Amar-Agnihotri]

Hi All,

I am using following dax to create a table --

Table F1 = SUMMARIZE (

SELECTCOLUMNS (

'Opportunity Snapshot',

"Source", 'Opportunity Snapshot'[Start of Quarter - Copy],

"Target", 'Opportunity Snapshot'[Flow one Result],

"Opportunity ID",'Opportunity Snapshot'[Opportunity_ID_18__c],

"Quarter",'Opportunity Snapshot'[Quarter]

),

[Source],

[Target],

[Opportunity ID],

[Quarter],

"Total", SUM ( 'Opportunity Snapshot'[Incremental ACV (USD)] )

)

This is based on the following table  --

Now rather then calculating Sum of the Incremental ACV i want to get that value of Incremental ACV which was entered on the latest date FOr example i want the value that is on 25 Mar 22 the last one. How can i get that in the above dax . Please help .

Thanks

[amitchandak]

@Amar-Agnihotri , Try measure like

calculate(suxm(Table, lastnonbalnkvalue(Table[Close_date_c], SUM ( 'Opportunity Snapshot'[Incremental ACV (USD)] ) ) ), allexcept(Table, Table[Opportunity ID]))

or

calculate(suxm(Table, lastnonbalnkvalue(Table[Close_date_c], SUM ( 'Opportunity Snapshot'[Incremental ACV (USD)] ) ) ), filter(allselected(Table), Table[Opportunity ID] = max(Table[Opportunity ID]) ))

[Amar-Agnihotri]

Thanks @amitchandak  for the reply but i think that would not work because There are many opportunity ids there in Snapshot table and I have to get the last value for each opportunity Id. The snap that i posted is just a demo snap for a single opportunity ID . You can see here . In this demo data i have taken two opportunities with different color. Now i want to pick the last ACV value for both the opportunites in the iteration.

[amitchandak]

@Amar-Agnihotri , The above should work

Measure =
VAR __id = MAX ('Table'[Opportunity ID] )
VAR __date = CALCULATE ( MAX('Table'[Close_date_c] ), ALLSELECTED ('Table' ), 'Table'[Opportunity ID] = __id )
CALCULATE ( SUM ( 'Opportunity Snapshot'[Incremental ACV (USD)] ), VALUES ('Table'[Opportunity ID] ),'Table'[Opportunity ID] = __id,'Table'[Close_date_c] = __date )

or

Measure =
VAR __id = MAX ('Table'[Opportunity ID] )
VAR __date = CALCULATE ( MAX('Table'[Close_date_c] ), ALLSELECTED ('Table' ), 'Table'[Opportunity ID] = __id )
Sumx ( VALUES ('Table'[Opportunity ID] ), CALCULATE ( SUM ( 'Opportunity Snapshot'[Incremental ACV (USD)] ), VALUES ('Table'[Opportunity ID] ),'Table'[Opportunity ID] = __id,'Table'[Close_date_c] = __date ) )

[Amar-Agnihotri]

Hi @amitchandak 

I created this measure --

Last ACV =

VAR _id = MAX ('Opportunity Snapshot'[Opportunity_ID_18__c])

VAR _date = CALCULATE( MAX('Opportunity Snapshot'[Close_Date__c]), ALLSELECTED ('Opportunity Snapshot'), 'Opportunity Snapshot'[Opportunity_ID_18__c] = _id)

RETURN

CALCULATE (SUM ( 'Opportunity Snapshot'[Incremental ACV (USD)] ), VALUES ('Opportunity Snapshot'[Opportunity_ID_18__c]),'Opportunity Snapshot'[Opportunity_ID_18__c] = _id,'Opportunity Snapshot'[Close_Date__c] = _date )

Now when i checked this for the particular Opportunity id = 0062A00000tGAMJQA4

The latest value  should be  500000 ( 5lac), But the measure is showing 4000000(40 lac) approximate 8 times of the actual value . I dont't know what is going wrong --

see the snap -

Actual Value  - 

[Amar-Agnihotri]

I haven't check the second measure.. Let me check that also 

[Amar-Agnihotri]

Hello @amitchandak  Both Measures are giving the wrong results

[Amar-Agnihotri · Accepted solution]

Hey @amitchandak  Final i resolved it . A small change need to be done 

Rather using Sum inside calculate we have to use SELECTEDVALUE inside calculate just like this --

Result is now correct -

Actual Value -

Both are same now . 
Thanks a lot for helping me