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.
Hello everybody,
Somebody knows, how can I use Fill Down with column formula?
I have 2 column, date and value:
1. 01/01/2017 - 500
2. 01/02/2017 - blank
3. 01/03/2017 - blank
4. 01/04/2017 - 1000
5. 01/04/2017 - blank
need to:
1. 01/01/2017 - 500
2. 01/02/2017 - 500
3. 01/03/2017 - 500
4. 01/04/2017 - 1000
5. 01/04/2017 - 1000
in this situation I can't use Power Query
thanks!
Solved! Go to Solution.
Hi @Anonymous
Try Using this calculated column
New Value = VAR LastNonBlankDate = CALCULATE ( LASTNONBLANK ( TableName[Date], 1 ), FILTER ( ALL ( TableName ), TableName[Date] <= EARLIER ( TableName[Date] ) && NOT ( ISBLANK ( TableName[Value] ) ) ) ) RETURN CALCULATE ( SUM ( TableName[Value] ), FILTER ( ALL ( TableName ), TableName[Date] = LastNonBlankDate ) )
Hi,
Using this same question as an example, does anyone knows how I can do up filling instead?
Meaning with date and value as below:
1. 01/01/2017 - 500
2. 01/02/2017 - blank
3. 01/03/2017 - blank
4. 01/04/2017 - 1000
I need:
1. 01/01/2017 - 500
2. 01/02/2017 - 1000
3. 01/03/2017 - 1000
4. 01/04/2017 - 1000
instead.. Thanks!!
Hi @Anonymous
Try Using this calculated column
New Value = VAR LastNonBlankDate = CALCULATE ( LASTNONBLANK ( TableName[Date], 1 ), FILTER ( ALL ( TableName ), TableName[Date] <= EARLIER ( TableName[Date] ) && NOT ( ISBLANK ( TableName[Value] ) ) ) ) RETURN CALCULATE ( SUM ( TableName[Value] ), FILTER ( ALL ( TableName ), TableName[Date] = LastNonBlankDate ) )
Hello, Zubair,
I am also looking for fill-up values solution. I carefully studied this code you suggested and it seemed to be no different from from fill-down. Could you please advice if this code is indeed different and what is the code for "fill-down" solution. Thank you
@Anonymous
Just wanted to share an option of a formula for fill-up, maybe someone will find it helpful:
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 |
---|---|
106 | |
93 | |
75 | |
62 | |
50 |
User | Count |
---|---|
146 | |
109 | |
106 | |
88 | |
61 |