How to categorize time elapsed since a date?

[Zelbinian]

I've been trying to figure this out for a day or so now. Some messages on this forum have gotten me closer but I'm still not quite there.

What I've got is a table (obviously) full of rows (obviously) that represent work items. One column is called "Changed Date" which is a datetime stamp of the last time this work item has been modified. This is connected to live data and so is updatable upon refresh.

What I want is to be able to categorize that data according to how far away that date is from today I want categories for within 10 days ago, 11-20 days ago, 21-30 days ago, and >30 days ago. These need to be static - I don't want the visualization redefining the bin size every time the data refreshes, I want it resorting the data into the proper bins.

What I've tried is a whole host of things, all of which involve me using the work item's unique ID in the "values" section of the vizualization, and some representation of the date column in the "Legend" section. I can't remember everything I've tried, but here are the highlights:

• I've tried calculating an Age column and then using that, but Power BI offers no default way to stratify the values.
• I've tried creating Groups from the visualization controls with both the Age and Changed Date as drivers
  • Bin group types aren't feasible - I want a set number of bins with a set size in each and this gives me one or the other
  • List groups seem to be what I want, except I can't define it with a query which is problematic. If there happen to be no dates in my sample data that are in the 11-20 bucket, I can't define it ahead of time with the controls offered. And, becuase of that, I'm not confident about how these groups will update as data changes.
• I've tried calculating a new column with a query on the imported data via DAX using the DATEDIFF function, but have been stymied by a SWITCH statement only allows switching on a single value instead of a range of values.

Thank you in advance for any advice and please let me know if I can clarify anything for anybody. 

[MFelix · Accepted solution]

Hi @Zelbinian,

You can create the column in M language or Dax then just use this column to make you visuals

M Language (query editor)
if Duration.Days(Date.From(DateTime.LocalNow()) - [Date]) <= 10 then "10 days ago" else
if Duration.Days(Date.From(DateTime.LocalNow()) - [Date]) > 10 and Duration.Days(Date.From(DateTime.LocalNow()) - [Date]) <= 20 then "20 days ago" else
if Duration.Days(Date.From(DateTime.LocalNow()) - [Date]) > 20 and Duration.Days(Date.From(DateTime.LocalNow()) - [Date]) <= 30 then "30 days ago" else
"> 30 day"



DAX

Bin =
VAR Date_Select =
    DATEDIFF ( Table1[Date]; TODAY (); DAY )
RETURN
    SWITCH (
        TRUE ();
        Date_Select <= 10; "10 days ago";
        Date_Select > 10
            && Date_Select <= 20; "20 days ago";
        Date_Select > 20
            && Date_Select <= 31; "30 days ago";
        "> 30 days"
    )

Regards,

MFelix

[Zelbinian]

I ended up using nested ifs in DAX, but I like that you figured out the switch statement. Can you explain how that switch statement works? That's a little different than the exemplars used in the Power BI documentation.

[MFelix]

Hi @Zelbinian,

You can also simplify the function by doing this:

Bin = 
VAR Date_Select =
    DATEDIFF ( Table1[Date]; TODAY (); DAY )
RETURN
    SWITCH (
        Date_Select > 0 ;
        Date_Select <= 10; "10 days ago";
        Date_Select <= 20; "20 days ago";
        Date_Select <= 31; "30 days ago";
        "> 30 days"
    )

When you use SWITCH you need to have an expression and then based on that expression you return the several result based on the comparision of values.

In the first case what I do is if the calculated variable return a value, in other words, if the calculation is TRUE then you categorize it by seeing if the variable is within the ranges selected, in other case go to the > 30 days.

If you see the one I wrote above I simplified it by removing the maximum value on each group and the first expression is if it's greater than 0 it will return the  > 30 days

Regards,

Mfelix