Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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!
Solved! Go to 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")
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,
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,
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")
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
113 | |
104 | |
77 | |
67 | |
63 |
User | Count |
---|---|
144 | |
107 | |
105 | |
82 | |
69 |