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

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

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!:
Mastering Power BI 2nd Edition

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!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Top Solution Authors