cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Eyal
Helper I
Helper I

find first date of values between -/+ 0.1.

I need a claculated colmun to identify the first date in a group were the value is between +/- 0.1.
Here is the data, the result Should be the dates indicated in arrow
my DAX:

 

CALCULATE(
     MIN('BR Data'[Date]),
         FILTER('BR Data',
         'BR Data'[Lot #]=EARLIER('BR Data'[Lot #]) &&
         'BR Data'[BR_Phase] < 0.1 && BR Data'[BR_Phase] > - 0.1
         )
)

 

Eyal_1-1606914910406.png

 

The intresting part is if i use a specfic value, it works

CALCULATE(MIN('BR Data'[Date]),
FILTER('BR Data',
'BR Data'[Lot #]=EARLIER('BR Data'[Lot #]) &&
'BR Data'[BR_Phase] =0.15
)
)
 
Any advise?
BR
Eyal
1 ACCEPTED SOLUTION
Eyal
Helper I
Helper I

Thank you both for your suggestions

@amitchandak the Measure you suggested works, but I need a calc column as this will surve as a slicer and grouping agent later on.
When I use you calculated colmun solution I get an error "Earlier/Earliest referes to an earlier context wich does not exist"

@V-lianl-msft 
 Using you solution for a claculated column I get back the first date of the Lot group, on the dates where the value is between +/- 0.1

Eyal_0-1607155890031.png

I didnt thought it was relevant on my original post, but I might be missing something.
the colmun "PhaseShift" is aclaculated colmun on its one, the calcualte the relative Log diffreance of a specfic atribute in each lot per day.
Maybe this is wnat cousing the problem???

thanks again for you help
eyal

View solution in original post

5 REPLIES 5
Eyal
Helper I
Helper I

Thank you both for your suggestions

@amitchandak the Measure you suggested works, but I need a calc column as this will surve as a slicer and grouping agent later on.
When I use you calculated colmun solution I get an error "Earlier/Earliest referes to an earlier context wich does not exist"

@V-lianl-msft 
 Using you solution for a claculated column I get back the first date of the Lot group, on the dates where the value is between +/- 0.1

Eyal_0-1607155890031.png

I didnt thought it was relevant on my original post, but I might be missing something.
the colmun "PhaseShift" is aclaculated colmun on its one, the calcualte the relative Log diffreance of a specfic atribute in each lot per day.
Maybe this is wnat cousing the problem???

thanks again for you help
eyal

View solution in original post

V-lianl-msft
Community Support
Community Support

Hi @Eyal ,

 

Based on your description, I have a little confused with your scenario, any chance you can share a PBIX file with dummy data?

Please remove any sensitive data before uploading.

 

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

HI @V-lianl-msft 

 

Just figured out what was the mistake from my side.
Your solution works perfeclty.
thank you

 

Eyal

V-lianl-msft
Community Support
Community Support

Hi @Eyal ,

 

Try this:

latest_date =
CALCULATE (
    FIRSTNONBLANK ( 'BR Data'[Date], 1 ),
    FILTER (
        'BR Data',
        'BR Data'[Lot#] = EARLIER ( 'BR Data'[Lot#] )
            && 'BR Data'[BR_Phase] > -0.1
            && 'BR Data'[BR_Phase] <= 0.1
    )
)

V-lianl-msft_0-1607066580005.png

 

 

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

amitchandak
Super User IV
Super User IV

@Eyal , Can check the data type once should work as new column like

 

new column =
CALCULATE(
MINX(FILTER('BR Data',
'BR Data'[Lot #]=EARLIER('BR Data'[Lot #]) &&
'BR Data'[BR_Phase] < 0.1 && 'BR Data'[BR_Phase] > - 0.1
)'BR Data'[Date])

)

 

And new measure like
new measure =
CALCULATE(
MINX(FILTER(allselected('BR Data'),
'BR Data'[Lot #]=max('BR Data'[Lot #]) &&
'BR Data'[BR_Phase] < 0.1 && 'BR Data'[BR_Phase] > - 0.1
)'BR Data'[Date])

)

 



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group

April Update

Check it Out!

Click here to read more about the April 2021 Updates!

secondImage

The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.

secondImage

Experience what’s next for Power BI

See the latest Power BI innovations, updates, and demos from the Microsoft Business Applications Launch Event.