Forum Discussion

jondeck24's avatar
jondeck24
Advocate II
9 years ago
Solved

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 thes...
Need Help
  • Phil_Seamark's avatar
    Phil_Seamark
    9 years ago

    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's avatar
    v-sihou-msft
    9 years ago

    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's avatar
Phil_Seamark
Microsoft Employee
9 years ago

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's avatar
    Phil_Seamark
    Microsoft Employee
    9 years ago

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