Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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 |
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.
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.
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.
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 @Anonymous
Your main question is differant than this now.
Could you please share sample data and expected output.
Thanks,
PRavin
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
113 | |
97 | |
80 | |
69 | |
59 |
User | Count |
---|---|
150 | |
119 | |
104 | |
87 | |
67 |