Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
mmoizk
Helper III
Helper III

Combining 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.

1 ACCEPTED SOLUTION
Sean
Community Champion
Community Champion

@mmoizk Let me know if this helps! You obviously have to pick better names for the Columns!

SWITCH.png

View solution in original post

16 REPLIES 16
Sean
Community Champion
Community Champion

@mmoizk Let me know if this helps! You obviously have to pick better names for the Columns!

SWITCH.png

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.  

 

TealCanady
Advocate II
Advocate II

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

Sean
Community Champion
Community Champion

Thanks Sean, how did u calcuated Start days ?

Sean
Community Champion
Community Champion

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)

Sean
Community Champion
Community Champion

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 TealDateDiffWarning.GIF

 

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?

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.