cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
AleksandrMe
Resolver I
Resolver I

Looking for Measure or Column formula to analyze only last row of same source

Good day All, 

 

I'm looking for Formula for new column.  below is data example: 

 

SHOPInspection Date Finding
SHOP 123-03-21Yes
SHOP 223-03-21No
SHOP 323-03-21Yes
SHOP 113-10-2020No 
SHOP 213-10-2020Yes
SHOP 313-10-2020Yes

 

What I will do, is the additional column with custom points for each SHOP.

For example, SHOP 1 inspected recently O points, if inspected more than 12 moth ago, then 1 point.

Another Column will add 1 point if finding is YES 

Finally last column will caclulate points and show where is most and this would need attention. 

 

Problem: Shops inspected and reinspected various times and once new insoection is done, old one is obsolete. So, I;m lloking for the function which reads only last source. Ideally i'm looking for the measure formula to put all together into separate visual. 

 

Thanks

1 ACCEPTED SOLUTION

2 REPLIES 2
Vera_33
Solution Sage
Solution Sage

Hi @AleksandrMe 

 

The previous Findings count 1 point or not? Something like this?

 

Vera_33_0-1624283151857.png

last Date = MAX(Table1[Inspection Date ])

test = 
VAR CurDate = [last Date]
VAR findings = IF(CALCULATE(MAX(Table1[Finding]),Table1[Inspection Date ]=CurDate)="Yes",1,0)
VAR last12month = IF(COUNTROWS(FILTER(Table1,Table1[Inspection Date ]<CurDate&&Table1[Inspection Date ]>EDATE(CurDate,-12)))>0,0,1)
RETURN
findings+last12month

Helpful resources

Announcements
PBI_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

MBAS on Demand

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

Get Ready for Power BI Dev Camp

Microsoft named a Leader in The Forrester Wave

Microsoft received the highest score of any vendor in both the strategy and current offering categories.

R2 (Green) 768 x 460px.png

Microsoft Dynamics 365 & Power Platform User Professionals

DynamicsCon is a FREE, 4 half-day virtual learning experience for 11,000+ Microsoft Business Application users and professionals.

Top Kudoed Authors