Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Trying to calculate days bucket for 2 dates against today's date. For example TargetStartdate and TargetEndDate. I did my dax for Start and end sperately and its working fine but I need to combine them For Start date DaysBucketStart = IF(1*(TODAY()-'TM Plan'[TargetStartDate]) < 30,"With in 30 days" ,IF(1*(TODAY()-'TM Plan'[TargetStartDate]) > 30 && 1*(TODAY()-'TM Plan'[TargetStartDate]) < 60,"With in 60 Days",IF(1*(TODAY()-'TM Plan'[TargetStartDate]) > 60 && 1*(TODAY()-'TM Plan'[TargetStartDate]) < 90,"With in 90 days","More than 90 days"))) For End Date DaysBucketEnd = IF(1*(TODAY()-'TM Plan'[TargetEndDate]) < 30,"With in 30 days" ,IF(1*(TODAY()-'TM Plan'[TargetEndDate]) > 30 && 1*(TODAY()-'TM Plan'[TargetEndDate]) < 60,"With in 60 Days",IF(1*(TODAY()-'TM Plan'[TargetEndDate]) > 60 && 1*(TODAY()-'TM Plan'[TargetEndDate]) < 90,"With in 90 days","More than 90 days"))) What I want is one Calculated Column for both , illustrating that in SQL Idea is to pick all the projects which are active today, todays date between StartDate and Enddate and which do fall in which bucket they fall 30,60 & 90.
Solved! Go to Solution.
@mmoizk Let me know if this helps! You obviously have to pick better names for the Columns!
@mmoizk Let me know if this helps! You obviously have to pick better names for the Columns!
Sean I liked your solution as well.
Thanks Sean, Just some clarifications. Days to Start and days to End are precalcuated days with datediff or 1*Today - startdate./endate. And can i combine results from both Start and ENd in one collective result
0-30 16
30-60 8
60-90 8
90 + 8
Gone 8
The idea is to encapsulate the requierement to one set of buckets for both dates.
Mmoizk,
I would create a calculated column using DateDiff; this will produce a new column with a value as the number of days from today that TargetStartDate. Alternatly you can use this in-line which may be optimal depending on your data.
Using the new column it would look something like this example (replacing the Fund Balance with your new Date Diff field or keeping it inline)
Fund Size:=SWITCH(TRUE(),
AND([Fund Balance]>=0, [Fund Balance]<=10000), “Up to $10,000”,
AND([Fund Balance]>=10001, [Fund Balance]<=50000), “$10,001 to 50,000”,
AND([Fund Balance]>=50001, [Fund Balance]<=100000), “$50,001 to 100,000”,
AND([Fund Balance]>=100001, [Fund Balance]<=500000), “$100,001 to 500,000”,
AND([Fund Balance]>=1500001, [Fund Balance]<=1000000), “$500,001 to 1,000,000”,
“greater than $1,000,000”
)
Hope this helps!
Teal Canady
Credit to the Power Pivot Pro Site for the example above
http://www.powerpivotpro.com/2012/06/dax-making-the-case-for-switch/
Thanks Teal , datediff isn't working for future dates
Can you show me your exact formula? DateDiff should be agnostic of future dates. =DateDiff(GetDate(),[EndDate],Day) should work; if you can send me the error / formula that would be great.
Teal
Thanks Sean, how did u calcuated Start days ?
I've put all formulas in the pictures? Which one do you mean?
Starts = COUNTA('Table'[START])
Days to Start and Days to End in your Excel (i know its based of the start date and today's date)
ALL calculations are in PBI!
Those are Calculated Columns in PBI!
Exactly , i was looking for what calculation were used to calcuate (Basically the formula ) did you use DateDiff or 1*(StartDate - today())
mmoizk,
I was actually asking for you calculation that was failing on datediff.
Teal
here you go Teal
Mmoizk,
Looks like Sean's solution is the only way to go; why on earth they limit datediff this way in DAX i can only guess. This should work for you sorry for the misleading post; never would have guessed on that limit (spent to much time in SQL).
Column:
DaysSinceStart = TMPlan[Target Start Date] - TODAY()
Teal
Exactly , want is the calucation done there?
User | Count |
---|---|
140 | |
113 | |
104 | |
76 | |
63 |
User | Count |
---|---|
135 | |
126 | |
110 | |
70 | |
61 |