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 have the current table below, which as you can see has missing values.
Car | Month | Value |
Fiat | Mar-08 | 10,000 |
Fiat | Apr-08 | |
Fiat | May-08 | |
Fiat | Jun-08 | 12,000 |
Fiat | Jul-08 | |
Fiat | Aug-08 | |
Audi | Mar-08 | 22,000 |
Audi | Apr-08 | |
Audi | May-08 | 24,000 |
Audi | Jun-08 | |
Audi | Jul-08 | 23,000 |
Audi | Aug-08 |
I need to create a calculated column which fills in those blanks based on the last month for that type of car, so something like this:
Car | Month | Value | Value 2 |
Fiat | Mar-08 | 10,000 | 10,000 |
Fiat | Apr-08 | 10,000 | |
Fiat | May-08 | 10,000 | |
Fiat | Jun-08 | 12,000 | 12,000 |
Fiat | Jul-08 | 12,000 | |
Fiat | Aug-08 | 12,000 | |
Audi | Mar-08 | 22,000 | 22,000 |
Audi | Apr-08 | 22,000 | |
Audi | May-08 | 24,000 | 24,000 |
Audi | Jun-08 | 24,000 | |
Audi | Jul-08 | 23,000 | 23,000 |
Audi | Aug-08 | 23,000 |
Does anyone have any ideas how to do this? The issue of offsetting is where there are more than two blank rows in sequence.
Thanks,
Jack
Solved! Go to Solution.
what do you mean by 'outside of Power Query'? is it calculated table?
in DAX you can try this, it works as long as Month column has type date
Value2 = VAR CurrentCar = 'Table'[Car] VAR CurrentDate = 'Table'[Month] VAR LastDateWithValue = CALCULATE ( MAX ( 'Table'[Month] ), FILTER ( 'Table', 'Table'[Value] <> BLANK () && 'Table'[Car] = CurrentCar && 'Table'[Month] <= CurrentDate ) ) RETURN CALCULATE ( SUM ( 'Table'[Value] ), FILTER ( 'Table', 'Table'[Car] = CurrentCar && 'Table'[Month] = LastDateWithValue ) )
if the goal is to have the table filled in you can achieve that in Power Query with few clicks
Unfortunately the table is outside of power query, is there a solution in DAX?
what do you mean by 'outside of Power Query'? is it calculated table?
in DAX you can try this, it works as long as Month column has type date
Value2 = VAR CurrentCar = 'Table'[Car] VAR CurrentDate = 'Table'[Month] VAR LastDateWithValue = CALCULATE ( MAX ( 'Table'[Month] ), FILTER ( 'Table', 'Table'[Value] <> BLANK () && 'Table'[Car] = CurrentCar && 'Table'[Month] <= CurrentDate ) ) RETURN CALCULATE ( SUM ( 'Table'[Value] ), FILTER ( 'Table', 'Table'[Car] = CurrentCar && 'Table'[Month] = LastDateWithValue ) )
Hi @Stachu
I tried your code, but it says a single value for column 'car' in table cannot be determined. could you please help
Hi @Stachu ,
I have same case, but the data type of column that need to fill is "Text? type. I try to using your dax but got an erro because SUM dax can used for data type of text.
basically I have column like this, I need to fill the "status" based on previous date with same school ID.
Any help will be really appreciate.
School ID | Date | Status |
1 | 1 nov 2021 | High |
2 | 1 nov 2021 | Medium |
3 | 1 nov 2021 | Low |
1 | 2 nov 2021 | High |
2 | 2 nov 2021 | Low |
3 | 2 nov 2021 | |
Hi,
I have a similar situation for me with forward filling of text based on conditions. The logic with SUM works well for numeric. Your solution will greatly help me. Thanks in Advance.
Regards.
I used your query and to me, it fills the previous value to blank rows but add some garbage value to existing row value
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 |
---|---|
117 | |
105 | |
69 | |
67 | |
43 |
User | Count |
---|---|
151 | |
103 | |
102 | |
87 | |
63 |