Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
8 years ago
Solved

How to categorize time elapsed since a date?

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 (obvious...
  • MFelix's avatar
    MFelix
    8 years ago

    Hi Anonymous,

     

    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

MFelix's avatar
MFelix
Icon for Super User rankSuper User
8 years ago

Hi Anonymous,

 

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

  • Anonymous's avatar
    Anonymous
    Not applicable
    8 years ago

    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's avatar
      MFelix
      Icon for Super User rankSuper User
      8 years ago

      Hi Anonymous,

       

      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