Forum Discussion

olimilo's avatar
olimilo
Post Prodigy
9 years ago
Solved

Creating bins from calculated column

I created a calculated column using the following DAX:

 

Days Elapsed (Deadline) = 
	SWITCH(
		TRUE(),
		Sheet1[CompleteByEndDate] < TODAY(), ABS(-1 * DATEDIFF(Sheet1[CompleteByEndDate], TODAY(), DAY)),
		Sheet1[CompleteByEndDate] > TODAY(), DATEDIFF(TODAY(), Sheet1[CompleteByEndDate], DAY)	
	)

Now, is it possible to create bins like for the days elapsed (eg: < 7, < 14, <31, <50, >50 days elapsed)?

Need Help
  • olimilo's avatar
    olimilo
    9 years ago

    Okay, I think I got it. Using this revised column for the Days Elapsed:

     

    Days Elapsed (Deadline) = 
	SWITCH(
		TRUE(),
		Sheet1[CompleteByEndDate] < TODAY(), DATEDIFF(Sheet1[CompleteByEndDate], TODAY(), DAY),
		Sheet1[CompleteByEndDate] > TODAY(), -1 * DATEDIFF(TODAY(), Sheet1[CompleteByEndDate], DAY)
	)

    I used this statement to make the bins and remove the Blanks (not late) in the Vis-level filter:

     

    Bins = 
	SWITCH(
		TRUE(),
		Sheet1[Days Elapsed (Deadline)] >= 1 && Sheet1[Days Elapsed (Deadline)] <= 7, "A",
		Sheet1[Days Elapsed (Deadline)] >= 8 && Sheet1[Days Elapsed (Deadline)] <= 14, "B",
		Sheet1[Days Elapsed (Deadline)] >= 15 && Sheet1[Days Elapsed (Deadline)] <= 21, "C",
		Sheet1[Days Elapsed (Deadline)] > 22, "D"
	)

1 Reply

  • olimilo's avatar
    olimilo
    Post Prodigy
    9 years ago

    Okay, I think I got it. Using this revised column for the Days Elapsed:

     

    Days Elapsed (Deadline) = 
	SWITCH(
		TRUE(),
		Sheet1[CompleteByEndDate] < TODAY(), DATEDIFF(Sheet1[CompleteByEndDate], TODAY(), DAY),
		Sheet1[CompleteByEndDate] > TODAY(), -1 * DATEDIFF(TODAY(), Sheet1[CompleteByEndDate], DAY)
	)

    I used this statement to make the bins and remove the Blanks (not late) in the Vis-level filter:

     

    Bins = 
	SWITCH(
		TRUE(),
		Sheet1[Days Elapsed (Deadline)] >= 1 && Sheet1[Days Elapsed (Deadline)] <= 7, "A",
		Sheet1[Days Elapsed (Deadline)] >= 8 && Sheet1[Days Elapsed (Deadline)] <= 14, "B",
		Sheet1[Days Elapsed (Deadline)] >= 15 && Sheet1[Days Elapsed (Deadline)] <= 21, "C",
		Sheet1[Days Elapsed (Deadline)] > 22, "D"
	)