cancel
Showing results for
Did you mean:
Super User

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

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

1 ACCEPTED SOLUTION
Super User

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

7 REPLIES 7
Super User

@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]) ))

Super User

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.

Super User

@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 ) )

Super User

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  -

Super User

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

Super User

Hello @amitchandak  Both Measures are giving the wrong results

Super User

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

Announcements

#### Power BI T-Shirt Design Challenge 2023

Vote for your favorite t-shirt design now through March 28.

#### Power BI March 2023 Update

Find out more about the March 2023 update.

#### March 2023 Events

Find out more about the online and in person events happening in March!

Top Solution Authors
Top Kudoed Authors