Forum Discussion
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 | 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 |
9 Replies
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.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 Can You explain with the exact source and output? not making it clear using two posts. Mark me @
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?
We typically create a date column on timestamp column and use that in time intelligence with date table
Date = Table[timestamp].date
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.
