cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
StephenJ
Regular Visitor

DAX expression to get the latest value of a field grouped by another field

My data is a history of stored file creation/deletion/storage tier. I'd like to show in my report the state of the storage at the date you filter by in the report. (I'm actually using datetimeoffset values, but Excel dates in example data for simplicity).

I managed to do this in powerquery with a parameter, sorting and group-by FIRST, but that requires you to edit the parameter and refresh the dataset every time you change it, which I'd like to avoid.

 

e.g. source data

idtierdatefilekey
1hot01-Apr1
2cold02-Apr1
3hot03-Apr2
4archive04-Apr1
5cold05-Apr2
6deleted06-Apr1
7hot07-Apr2
8deleted08-Apr2

 

 

filtering by 01-Apr would give

tierdatefilekey
hot01-Apr1

 

filtering by 03-Apr would give

tierdatefilekey
cold02-Apr1
hot03-Apr2

 

filtering by 08-Apr or any date after this would give

tierdatefilekey
deleted06-Apr1
deleted08-Apr2
1 ACCEPTED SOLUTION
MFelix
Super User
Super User

Hi @StephenJ ,

 

Add a disconnected table with the dates then add the followiing two measures: 

 

MaxDate = CALCULATE(MAX('Table'[date]),'Table'[date] <= MAX(Dates[Date]))

 

MaxTier =
VAR MaximumDateSelection = [MaxDate]
RETURN
    MAXX (
        FILTER (
            SUMMARIZE ( 'Table', 'Table'[date], 'Table'[filekey], 'Table'[tier] ),
            'Table'[date] = MaximumDateSelection
        ),
        'Table'[tier]
    )

Should give expected result:

MFelix_1-1651271389247.pngMFelix_2-1651271402695.png

 

MFelix_3-1651271412924.png

 

 


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



View solution in original post

1 REPLY 1
MFelix
Super User
Super User

Hi @StephenJ ,

 

Add a disconnected table with the dates then add the followiing two measures: 

 

MaxDate = CALCULATE(MAX('Table'[date]),'Table'[date] <= MAX(Dates[Date]))

 

MaxTier =
VAR MaximumDateSelection = [MaxDate]
RETURN
    MAXX (
        FILTER (
            SUMMARIZE ( 'Table', 'Table'[date], 'Table'[filekey], 'Table'[tier] ),
            'Table'[date] = MaximumDateSelection
        ),
        'Table'[tier]
    )

Should give expected result:

MFelix_1-1651271389247.pngMFelix_2-1651271402695.png

 

MFelix_3-1651271412924.png

 

 


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



Helpful resources

Announcements
September Update

Check it Out!

Click here to learn more about the September 2022 updates!

Power BI Dev Camp Session 26

Check it Out!

Mark your calendars and join us on Thursday, September 29 at 11a PDT for a great session with Ted Pattison!

Top Solution Authors