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

Reporting the outcome recorded for the max date within the reporting period

I need to be able to say the outcome recorded for the max date within the reporting period. The max date sits on 1 table and the outcome on another table. 

 

I have the max date:

DRAFT Accomodation situation at end of reporting period? =

var _enddatecontext=MAX('transform DimCalendar'[ActualDate])

Return

CALCULATE(

MAX('dl FactCATServiceReview'[CATServiceReviewReviewDate])

,FILTER('dl FactCATServiceReview',

('dl FactCATServiceReview'[CATServiceReviewReviewDate]<=_enddatecontext)))

 

But then I need to say look at the related table and return the status text.

Linking column [AccommodationStatusID]

Table: dl_DimCATReviewAccommodationStatus

Column: [CATReviewAccommodationStatusText]

1 ACCEPTED SOLUTION
hanzfunny
Frequent Visitor

SOLVED.
I merged the tables to exclude the related table look-up problem.
Then turned the calculation into a variable. 

DRAFT Accomodation End =

var _enddatecontext=MAX('transform DimCalendar'[ActualDate])

var _MaxDate=CALCULATE(MAX('dl FactCATServiceReview'[CATServiceReviewReviewDate]),FILTER('dl FactCATServiceReview','dl FactCATServiceReview'[CATServiceReviewReviewDate]<=_enddatecontext))

var _Result=

CALCULATE(MAX('dl FactCATServiceReview'[AccommodationSituation]),'dl FactCATServiceReview'[CATServiceReviewReviewDate]=_MaxDate)

 

RETURN

_Result

 

View solution in original post

1 REPLY 1
hanzfunny
Frequent Visitor

SOLVED.
I merged the tables to exclude the related table look-up problem.
Then turned the calculation into a variable. 

DRAFT Accomodation End =

var _enddatecontext=MAX('transform DimCalendar'[ActualDate])

var _MaxDate=CALCULATE(MAX('dl FactCATServiceReview'[CATServiceReviewReviewDate]),FILTER('dl FactCATServiceReview','dl FactCATServiceReview'[CATServiceReviewReviewDate]<=_enddatecontext))

var _Result=

CALCULATE(MAX('dl FactCATServiceReview'[AccommodationSituation]),'dl FactCATServiceReview'[CATServiceReviewReviewDate]=_MaxDate)

 

RETURN

_Result

 

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.