Forum Discussion
Time Buckets
Hey All,
Had a quick question on how to write a DAX formula to create some time buckets. So I have a submitted date field in my data that goes back every day for 6 months. I need to bucket these into 30 days, 30-60 days, and 60+. The table is application data with various data points and the point is to give #'s as in how many apps have been in the system for 30 days or less, 30-60 days, and how many apps are 60+ days out. I thought of using Now or Today but am unsure how to reference it off the appsubmitteddate field.
Thanks!
One formula you can tweak might be along these lines :
Date Buckets = SWITCH (
INT(divide(now() - int('Dates'[Date].[Date]),30)) ,
0 , "Current" ,
1 ,"30 to 60" ,
2 , "60 to 90" ,
// else ...
"other")In this scenario, to determine which bucket, you should add a column to calculate the variance. You can directly use Table[Date] minus TODAY() as Phil_Seamark suggested or use DATEDIFF().
Variance = DATEDIFF(Table[Date],TODAY(),DAY)
Then specify different bucket with above column as condition.
Regards,
3 Replies
I'd suggest building a separate Date table that contains 1 row per date and add columns to this table and creating a relationship.
You can build dynamic DAX forumulas to bucket your data as appropriate.
A common column to add might be
Days from Today = int(dates[date] - now())
but you can create variations using SWITCH or nested IF statements.
One formula you can tweak might be along these lines :
Date Buckets = SWITCH (
INT(divide(now() - int('Dates'[Date].[Date]),30)) ,
0 , "Current" ,
1 ,"30 to 60" ,
2 , "60 to 90" ,
// else ...
"other")
In this scenario, to determine which bucket, you should add a column to calculate the variance. You can directly use Table[Date] minus TODAY() as Phil_Seamark suggested or use DATEDIFF().
Variance = DATEDIFF(Table[Date],TODAY(),DAY)
Then specify different bucket with above column as condition.
Regards,