Forum Discussion
Helper INew field based on date range
Hi everyone!
I have a question and need some assistance. I need to create a new field based on a date range.
Ex:
Column = IF (DATESBETWEEN 1/01/17, 01/31/17), VALUE1, IF (DATESBETWEEN 02/01/17, 02/28/17), VALUE2))
Any assistance would be appreciated. Thanks!
Babinsky
8 Replies
- Baskar
Resident Rockstar
Cool dude,
Create one measure
Measure =
Switch ( True() ,
AND( Date > 1/01/17, Date < 01/31/17 ) , Value1,
AND( Date > 2/01/17, Date < 02/28/17 ) , Value2,
AND( Date > 3/01/17, Date < 03/31/17 ) , Value3,
.
.
. etc
)
Try this it will help u , if not let me know i will help u dude.
If you're only interested in Jan 2017 and Feb 2017 and Mar 2017, then the approach from Baskar should work, though you might need to adapt it a little with a MAX and >= and <= in place of > and < respectively - e.g.
DateValue =
SWITCH ( TRUE() ,
AND ( MAX([Date]) >= DATE(2017,1,1), MAX([Date]) <= DATE(2017,1,31) ), "Value1",
AND ( MAX([Date]) >= DATE(2017,2,1), MAX([Date]) <= DATE(2017,2,28) ), "Value2",
AND ( MAX([Date]) >= DATE(2017,3,1), MAX([Date]) <= DATE(2017,3,31) ), "Value3",
BLANK()
)If you're interested in years aside from 2017 or months past March, there may be a better solution depending on what you're trying to do.
- Babinsky
Unfortunity it didn't work.
Baskar, can you contact me? I sent you a message.
- Babinsky
- Babinsky
Anyone who can help, for the following code below what is the syntax for the date field?
Measure =
Switch ( True() ,
AND( Date > 1/01/17, Date < 01/31/17 ) , Value1,
AND( Date > 2/01/17, Date < 02/28/17 ) , Value2,
AND( Date > 3/01/17, Date < 03/31/17 ) , Value3)
For Example:
File - PM022BIWK
Field - PDATCR
v-sihou-msftMicrosoft Employee
Since you need to do comparison on row level, you should create a calculated column instead of measure with formula as Baskar posted.
It can be like:
Column = SWITCH( TRUE(), 'Table'[Date]>=DATE(2017,1,1) && 'Table'[Date]<=DATE(2017,1,31),"Value1", 'Table'[Date]>=DATE(2017,2,1) && 'Table'[Date]<DATE(2017,2,28),"Value2", .... )
See my sample below:
Regards,
