Time Buckets

[jondeck24]

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!

[Phil_Seamark · Accepted solution]

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")

[v-sihou-msft · Accepted solution]

@jondeck24

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,

[Phil_Seamark]

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.

[Phil_Seamark · Accepted solution]

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")