Shift value in a column by Dax function

[Anonymous]

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	f	f
7	g	g
8	h	h
9	i	i

Desired result after shifted down 4 rows:

Column 1	Column 2	Column 3
1	a	a
2	b	b
3	c	c
4	d	d
5	a	e
6	b	f
7	c	g
8	d	h
9	e	i

[Anonymous]

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.

Time	Count
01/01/2018 10:00:00 PM	4
01/01/2018 11:00:00 PM	2
02/01/2018 12:00:00 AM	2
02/01/2018 01:00:00 AM	6
02/01/2018 02:00:00 AM	4
02/01/2018 03:00:00 AM	9
02/01/2018 04:00:00 AM	0

How can I calculate previous period with this table?

[amitchandak]

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

Date = Table[timestamp].date

[Anonymous]

Hello @amitchandak ,

I am struggling with this case.

I current ly have a table like this.

Time	Count	Expected (if shift 3 rows)
01/01/2018 10:00:00 PM	4
01/01/2018 11:00:00 PM	2
02/01/2018 12:00:00 AM	2
02/01/2018 01:00:00 AM	6	4
02/01/2018 02:00:00 AM	4	2
02/01/2018 03:00:00 AM	9	2
02/01/2018 04:00:00 AM	0	6

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.

[amitchandak]

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.

[Anonymous]

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.

[Anonymous]

Hi @Anonymous 

Try this

create one column

if(table[Column1]<=4,Column2,Lookupvalue(table[Column2],table[Column1],table[Column1]-4)

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

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

[Anonymous]

Hello @Anonymous ,

Can we express it as a Measure like DATEADD or PARALLELPRERIOD?

In detail, I want to calculate a total Count with Previous Date Period but My data drilled down by Hour. When I use DATEADD or PARALLELPRERIOD, in thrown an exception because column date has duplicated value.

My date by count like 

Time	Count
01/01/2018 10:00:00 PM	4
01/01/2018 11:00:00 PM	2
02/01/2018 12:00:00 AM	2
02/01/2018 01:00:00 AM	6
02/01/2018 02:00:00 AM	4
02/01/2018 03:00:00 AM	9
02/01/2018 04:00:00 AM	0

[amitchandak]

Can You explain with the exact source and output?  not making it clear using two posts. Mark me @

[Anonymous]

Hi @Anonymous 

Your main question is differant than this now.

Could you please share sample data and expected output.

Thanks,

PRavin