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

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
Alice_Cooper
Helper II
Helper II

How to return value of a field based on Latest Record Date

Hi Guys,

I have come across a problem and would really like some help.

I have data set HLPropertyDataEntry three fields are in question here. EndOfMonthReportPeriod, TotalProperties and TotalActiveProperties .... The visualisation is not filtered. The visualisation returns data from two other datasets that contain only one record (HLPartyDataEntry and HLFundingDataEntry). The values for these in the snippet below are 100, 85 and the $1,475,629 ... These are OK.

 

The Datatset HLPropertyDataEntry is the problem... I want to display a Card that gives me the Latest values of TotalProperties and TotalActiveLeases based on EndOfMonthReportPeriod.

 

The data is as per the Matrix where September 2023 should have 32 as Total Properties and 25 as TotalActiveLeases ... When using the Card Viz for TotalActiveLeases I cannot get the latest record ... using Max returns 28 ... Can someone help me please?

 

Here is teh data:

 

YearMonthTotal PropertiesTotal Active Leases
2022July33
2022August55
2022September88
2022November1010
2023January1312
2023February1514
2023March1918
2023April2120
2023May2423
2023June2624
2023July2926
2023August3128
2023September3225

 

 

Alice_Cooper_0-1669071449938.png

 

1 ACCEPTED SOLUTION
Greg_Deckler
Super User
Super User

@Alice_Cooper Assuming you have a Date column, try:

Measure = 
  VAR __Date = MAXX(ALL('Table'),[Date])
  VAR __MaxValue = MAXX(FILTER(ALL('Table'),[Date] = __Date),[Total Active Leases])
RETURN 
  __MaxValue

@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...

View solution in original post

2 REPLIES 2
Alice_Cooper
Helper II
Helper II

Fantastic.... I am very appreciative... Have been working on this for days ... Works a treat .... You are a Legand.... 

Greg_Deckler
Super User
Super User

@Alice_Cooper Assuming you have a Date column, try:

Measure = 
  VAR __Date = MAXX(ALL('Table'),[Date])
  VAR __MaxValue = MAXX(FILTER(ALL('Table'),[Date] = __Date),[Total Active Leases])
RETURN 
  __MaxValue

@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...

Helpful resources

Announcements
LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.