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
KeithC
Frequent Visitor

Return value associated with latest date

Hi All

 

I am still relatively new in my DAX journey and looking for some assitance. I have a snippet of a table below and I have tried to create a calculated measure that

 

1. sum of the count  for latest date in table so below this would be 2 (6th Dec)

2. sum of the count  for second date in table so below this would be 4 (3rd Dec)

 

 

KeithC_1-1667904030940.png

 

I managed to get the required figure for point 1 but want this to be independent of other variables so always remains constant

 

Latest CMI Count = CALCULATE('Table'[Count Total],filter(ALL('Table'[Date]),'Table'[Date]=max('Table'[Date])))

 

Thanks in advance!

1 ACCEPTED SOLUTION

This will ignore any filters from anywhere

Prev CMI Count =
VAR MaxDate =
    CALCULATE ( MAX ( 'Table'[Date] ), REMOVEFILTERS () )
VAR PrevDate =
    CALCULATE ( MAX ( 'Table'[Date] ), 'Table'[Date] < MaxDate, REMOVEFILTERS () )
VAR Result =
    CALCULATE ( [Count total], REMOVEFILTERS (), 'Table'[Date] = PrevDate )
RETURN
    Result

View solution in original post

4 REPLIES 4
johnt75
Super User
Super User

Try

Prev CMI Count =
VAR MaxDate =
    MAX ( 'Table'[Date] )
VAR PrevDate =
    CALCULATE ( MAX ( 'Table'[Date] ), 'Table'[Date] < MaxDate )
VAR Result =
    CALCULATE ( [Count total], REMOVEFILTERS ( 'Table' ), 'Table'[Date] = PrevDate )
RETURN
    Result

Thanks @johnt75 

 

Have tried this to get the max and the second max and have obtained the correct counts but these change if I have any slicers on my dashboard. Can the DAX formula be modified so the result is constant?

This will ignore any filters from anywhere

Prev CMI Count =
VAR MaxDate =
    CALCULATE ( MAX ( 'Table'[Date] ), REMOVEFILTERS () )
VAR PrevDate =
    CALCULATE ( MAX ( 'Table'[Date] ), 'Table'[Date] < MaxDate, REMOVEFILTERS () )
VAR Result =
    CALCULATE ( [Count total], REMOVEFILTERS (), 'Table'[Date] = PrevDate )
RETURN
    Result

Excellent @JohnT this has now worked and above is understood. Thanks again!

 

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.

Top Solution Authors