Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Solved! Go to 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....
Proud to be a PBI Community Champion
@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
Proud to be a PBI Community Champion
@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....
Proud to be a PBI Community Champion
i was able to get it to work thanks alot!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
110 | |
99 | |
80 | |
64 | |
57 |
User | Count |
---|---|
145 | |
110 | |
91 | |
84 | |
66 |