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
Anonymous
Not applicable

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

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
Community Champion
Community Champion

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
Anonymous
Not applicable

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 )




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 )

 

Anonymous
Not applicable

@Cmcmahan @tex628 @mochabits 

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


tex628
Community Champion
Community Champion

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


Connect on LinkedIn

@Anonymous 
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
March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

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.

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.