Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Amar-Agnihotri
Resolver I
Resolver I

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  --
 
AmarAgnihotri_0-1634794193122.png

 

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

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

 

AmarAgnihotri_0-1634802451495.png

Result is now correct -

AmarAgnihotri_1-1634802505410.png

Actual Value -

AmarAgnihotri_2-1634802543563.png

Both are same now . 
Thanks a lot for helping me 

View solution in original post

7 REPLIES 7
amitchandak
Super User
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]) ))

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.

 

AmarAgnihotri_0-1634797447656.png

 

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

 

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 -
AmarAgnihotri_0-1634800757786.png

 

Actual Value  - 

 

AmarAgnihotri_1-1634800843817.png

 

 

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

Hello @amitchandak  Both Measures are giving the wrong results

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

 

AmarAgnihotri_0-1634802451495.png

Result is now correct -

AmarAgnihotri_1-1634802505410.png

Actual Value -

AmarAgnihotri_2-1634802543563.png

Both are same now . 
Thanks a lot for helping me 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.