Forum Discussion
Solved
Using Dax only create an index column
Consider Table1 below , also note I'm not able to use Power Query currently for below report. user_key FYear 001 14/15 Q4 002 15/16 Q1 003 15/16 Q1 004 15/16 ...
Hi, giordafrancis
I only know how to create this table in two steps. I could not find a way to create it in one step.
step 1. create a Table
new table = SUMMARIZE('Table', 'Table'[FYear])Step 2. create a calculated columidx =
CALCULATE (
SUMX ( 'new table', 1 ),
FILTER ( 'new table', 'new table'[FYear] <= EARLIER ( 'new table'[FYear] ) )
)Hi, My name is Jihwan Kim.
If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.
Linkedin: https://www.linkedin.com/in/jihwankim1975/
Hi, giordafrancis
I only know how to create this table in two steps. I could not find a way to create it in one step.
step 1. create a Table
new table = SUMMARIZE('Table', 'Table'[FYear])
Step 2. create a calculated colum
idx =
CALCULATE (
SUMX ( 'new table', 1 ),
FILTER ( 'new table', 'new table'[FYear] <= EARLIER ( 'new table'[FYear] ) )
)
CALCULATE (
SUMX ( 'new table', 1 ),
FILTER ( 'new table', 'new table'[FYear] <= EARLIER ( 'new table'[FYear] ) )
)
Hi, My name is Jihwan Kim.
If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.
Linkedin: https://www.linkedin.com/in/jihwankim1975/
Here's how to do it in one go:
[New Table] =
addcolumns(
distinct( YourTable[FYear] ),
"idx",
var CurrentFYear = YourTable[FYear]
return
countrows(
summarize(
filter(
YourTable,
YourTable[FYear] <= CurrentFYear
),
YourTable[FYear]
)
)
)