Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago

Shift value in a column by Dax function

Hi all,   I would like to know Is there any function in Dax help shift values in a column. For example: Column 1 Column 2 Column 3 1 a a 2 b b 3 c c 4 d d 5 e e 6...
Need Help
Anonymous's avatar
Anonymous
Not applicable
6 years ago

Hi amitchandak  and Anonymous ,

 

Sorry for make you confused.

I current have data of count drilled down by hour.

I know in DAX have a function to calculate same previous date or month (DATEADD or PARALELLPERIOD)

But this two functions cannot use within a column have duplicated values, and my column date has data/time date so it has duplicate date values.

TimeCount
01/01/2018 10:00:00 PM4
01/01/2018 11:00:00 PM2
02/01/2018 12:00:00 AM2
02/01/2018 01:00:00 AM6
02/01/2018 02:00:00 AM4
02/01/2018 03:00:00 AM9
02/01/2018 04:00:00 AM0

 

How can I calculate previous period with this table?

amitchandak's avatar
amitchandak
Super User
6 years ago

We typically create a date column on timestamp column and use that in time intelligence with date table

Date = Table[timestamp].date

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    Hello amitchandak ,

     

    I am struggling with this case.

    I current ly have a table like this.

    TimeCountExpected (if shift 3 rows)
    01/01/2018 10:00:00 PM4 
    01/01/2018 11:00:00 PM2 
    02/01/2018 12:00:00 AM2 
    02/01/2018 01:00:00 AM64
    02/01/2018 02:00:00 AM42
    02/01/2018 03:00:00 AM92
    02/01/2018 04:00:00 AM06

    How can I use DATEADD function with this table?

    In my topic, you mentioned use 

    Date = Table[timestamp].date

    Could you please help me explain more how to use this?

    I want to calculate a count shifted by specific days, just like DATEADD function.

     

    Thank you for your help.

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      Hi Anonymous 

       

      Create one index column and add the formula which i have suggested.

       

      I don't think using measures you can acheive this.

       

      Thanks & regards,
      Pravin Wattamwar
      www.linkedin.com/in/pravin-p-wattamwar

      If I resolve your problem Mark it as a solution and give kudos.

    • amitchandak's avatar
      amitchandak
      Super User
      6 years ago

      Try like

      rank = rankx(all(table),table[Time],,asc,dense)
      In the below 2 second one should work
      expected = maxx(filter(table,table[rank]=earlier(table[rank])-3),table[count])

      //OR
      expected = maxx(filter(table,table[rank]-3=earlier(table[rank])),table[count])

       

      Appreciate your Kudos.