Forum Discussion
Helper IIICombining 2 different if statements in one
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.
16 Replies
mmoizkThanks 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.
TealCanadyAdvocate II
Sean I liked your solution as well.
- TealCanady
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/
