Forum Discussion
Select Min Date Based on Calculations
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.
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....
4 Replies
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
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).
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....
