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

Determine if a certain value returned within a time period (date stored in same column)

Hi, 

 

I am quite new to PowerBi and I have been trying to solve this myself without any luck.
In the data set I have which has more columns than displayed below we capture when a serial number is repaired with corresponding case number. 
It might be that the same serial number & case are worked on multiple dates, which generates multiple lines in the dataset. 

I am looking for an option to register a hit if a Serial Number repaired back within 6 months of last repair date with a different case number. The hit would need to be registered at the date it was first repaired again. 
Below is a sample of how the data could look like I have added the same Serial Number 5 times with 3 different cases.
In the last column I displayed how the "hit" could be registered. 

 

It might be that I am thinking too complicated and that this is easy to solve.

Hope somebody is able to help me with this

 

Case NumberSerial NumberDate RepairReturned in 6 Months
X12345U765010-5-20190
X12345U765011-5-20190
X12347U765210-6-20190
X12348U765311-6-20190
X12349U765412-6-20190
X12350U765513-6-20190
X12351U765614-6-20190
X12352U765715-6-20190
X12353U765010-7-20191
X12353U765011-7-20190
X12355U766011-7-20190
X12356U765011-7-20200

 

1 ACCEPTED SOLUTION
Cmcmahan
Resident Rockstar
Resident Rockstar

It looks like he just forgot a CALCULATE/FILTER syntax. I've fixed your query to (hopefully) be free of syntax errors.

 

Column = 
VAR date_ = [DateOfRepair]
VAR c6_mon = Date(YEAR(date_) ; MONTH(Date_)-6 ; DAY(date_))
VAR case6 = [PSSCASENUMBER]
return
IF(
CALCULATE(
Countrows('repairs (2)');
FILTER( ALL('repairs (2)');
[SerialNumber] = EARLIER([SerialNumber]) &&
[CASENUMBER] <> case6 &&
[DateOfRepair] < date_ &&
[DateOfRepair] >= c6_mon
)
) > 0 ; 1 ; 0 )

 

View solution in original post

7 REPLIES 7
tex628
Super User
Super User

Column = 
date_ = [Date repair]
VAR 6_mon = Date(YEAR(date_) , MONTH(Date_)-6 , DAY(date_))
VAR case = [Case number] return IF(
Calculate( Countrows('table'), all(table), [serial number] = earlier([serial number]) ,
[Case number] <> case [Date Repair] < date_ , [Date Repair] >= 6_mon ) > 0 , 1 , 0 )

Connect on LinkedIn
Damo92
Regular Visitor

Hi tex628,

 

Thank you for your help!
I am getting 2 errors which I need some additional help with


1. For the IF formula that more than 3 arguments are used,

I have noticed that Countrows is seen as Logical test and All is seen as value if true, resulting in Serial Number being seen as value if false


2. In the EARLIEST[SERIALNR] it is stating that parameter is not the correct type, can this because the column is stored as text?
 it is stored as text due to the fact that the serial number contains letters too

Below is how the DAX is looking for me now:

Column = 
VAR date_ = [DateOfRepair]
VAR c6_mon = Date(YEAR(date_) ; MONTH(Date_)-6 ; DAY(date_))
VAR case6 = [PSSCASENUMBER]
return
IF(
Countrows('repairs (2)');
all('repairs (2)');
[SerialNumber] = EARLIER([SerialNumber] ;
[CASENUMBER] <> case6
[DateOfRepair] < date_ ;
[DateOfRepair] >= c6_mon
) > 0 ; 1 ; 0 )




Cmcmahan
Resident Rockstar
Resident Rockstar

It looks like he just forgot a CALCULATE/FILTER syntax. I've fixed your query to (hopefully) be free of syntax errors.

 

Column = 
VAR date_ = [DateOfRepair]
VAR c6_mon = Date(YEAR(date_) ; MONTH(Date_)-6 ; DAY(date_))
VAR case6 = [PSSCASENUMBER]
return
IF(
CALCULATE(
Countrows('repairs (2)');
FILTER( ALL('repairs (2)');
[SerialNumber] = EARLIER([SerialNumber]) &&
[CASENUMBER] <> case6 &&
[DateOfRepair] < date_ &&
[DateOfRepair] >= c6_mon
)
) > 0 ; 1 ; 0 )

 

@Cmcmahan @tex628 @mochabits 

Thanks to all of you for helping me out on this!
It works like a charm now!


Hahaha missing CALCULATE() indeed! 🙂 Thank you for catching it!


Connect on LinkedIn

returned? =
var currDate = 'case'[dateRepair]
var mon_6 = DATE(YEAR(currDate), MONTH(currDate)-6,DAY(currDate))
return if (
CALCULATE( COUNTROWS('case'),FILTER(ALL('case'),
'case'[Serial Number]=EARLIER('case'[Serial Number]) &&
'case'[Case Number]<> EARLIER('case'[Case Number]) &&
'case'[dateRepair] < currDate &&
'case'[dateRepair] >= mon_6)) > 0, 1,0)
dax
Community Support
Community Support

HiDamo92,

 

Sorry for my bad understanding, I am not clear about your logic for your “Returned in 6 month”. If possible, could you please explain this for me by more detailed sample or information?

What did you mean of “if a Serial Number repaired back within 6 months of last repair date with a different case number. ” and “The hit would need to be registered at the date it was first repaired again. “?

 

If you want to record serial number which will return  back, why the last row not show 1?

54.png

Best Regards,

Zoe Zhi

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

Helpful resources

Announcements
Microsoft Build 768x460.png

Microsoft Build is May 24-26. Have you registered yet?

Come together to explore latest innovations in code and application development—and gain insights from experts from around the world.

May 23 2022 epsiode 5 without aka link.jpg

The Power BI Community Show

Welcome to the Power BI Community Show! Jeroen ter Heerdt talks about the importance of Data Modeling.

Power BI Dev Camp Session 22 with aka link 768x460.jpg

Check it out!

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

charticulator_carousel_with_text (1).png

Charticulator Design Challenge

Put your data visualization and design skills to the test! This exciting challenge is happening now through May 31st!