Select Min Date Based on Calculations

[reggiete]

Hello All, 

I am trying to write some logic in Dax, to get the minimum date where the total open is greater than 5% of the total open volume. 

In the screenshot below, if i just use min(Date) it will retrieve 9/27/18, but theres only 3 open from 9/27 and it only represents 1% of the total open (225). I am needing to bring back 9/28/18 since its the oldest date and it represents more then 5% of the total open. 

[PattemManohar]

@reggiete Please try creating a new "Measure" as below

MinDate = 
VAR _Total = SUM(TestDates[TotalOpen])
VAR _FivePercentOfTotal = _Total * 0.05
VAR _MinDate = CALCULATE(MIN(TestDates[Date]), FILTER(TestDates, TestDates[TotalOpen]>=_FivePercentOfTotal))
RETURN _MinDate

[reggiete]

@PattemManohar so where we are applying filter on _min date, i need it to calc the percentage by day. 

Example 

For Date 9/27 - 3 open out of 225. Which equals 1% = Not min date. 

For Date 9/28 - 26 out of 225 which equals 12% = min date because total open for this date represents 5% of the total open. 

 I am not concerned with anything that is less than 1 day.  Days buckets is a group i created based on day count. So anything showing Day 0 means its todays date, day 1 meaning 1 day has passed (10/2/18). 

[PattemManohar · Accepted solution]

@reggiete  Not sure whether you tried this or not... I've tried the same with the sample data posted and got the expected output...

_Total Calculates the SUM of TotalOpen

_FivePercentofTotal Gives the 5% of the _Total

_MinDate Calculates the Minimum Date outof the records that contain TotalOpen >= 5% of TotalOpen

Finally, returning the MinDate as the output. 

Hope this helps !! Let me make it clear, if my understanding of your requirement is wrong....

[reggiete]

i was able to get it to work thanks alot!