Fill out empty rows with data from other colums

[BieBel]

I have a set of data registrations for several months => (value_reported) for every (yyyymm). Yet, for some months there has'nt been any registration. I would like to/need to fill out all the 'gaps' in my matrix with either the next available or the previous available value. So I would need to create a new columns/measure replacing every blank with either the former or the folowwing value (one of both will do). The 'snip' is from the 'matrix' visual.

Can anybody tell me what formula would return one of the expected values?

[v-kongfanf-msft]

Hi @BieBel ,

Did Sergii24 reply solve your problem? If so, please mark it as the correct solution, and point out if the problem persis

Best regards,

Adamk Kong

[Sergii24 · Accepted solution]

Hi @BieBel, you need to create a measure with the following logic:

Measure Value Adjusted =
VAR _Value = [Measure Value]                          //value of a measure you're interested in   
VAR _yyyymm = SELECTEDVALUE( CalendarTable[yyyymm] )  //currently selected yymmmm
VAR _yyyymm_Previous =                                //find prevoius _yyyymm
   MAXX(
     CALCULATETABLE(
       SELECTEDVALUE( CalendarTable[yyyymm] ),
       CalendarTable[mmyyyy] < _yyyymm
     ),
     [yyyymm]
   )

RETURN
   IF(
     _Value <> BLANK(),
     _Value,
     CALCULATE(
       [Measure Value],
       CalendarTable[mmyyyy] = _yyyymm_Previous
     )
   )

I hope it helps! Good luck with your project 🙂

[Swapnilmandloi]

Hello,

To fill in the gaps in your matrix with either the next available or previous available value, you can use a DAX measure that implements the COALESCE function along with the LOOKUPVALUE function. Here's a step-by-step solution:

1. First, create a calculated column in your date table (if you don't have one, create a continuous date table) to represent each month:

Date Column = DATE(YEAR([Your Date Column]), MONTH([Your Date Column]), 1)

1. Then, create a measure that will fill in the blanks with either the next or previous non-blank value:

Filled Value = VAR CurrentValue = SUM([value_reported]) RETURN IF( NOT(ISBLANK(CurrentValue)), CurrentValue, COALESCE( LOOKUPVALUE( [value_reported], [Date Column], MINX(FILTER(ALL([Date Column]), [Date Column] > MAX([Date Column])), [Date Column]) ), LOOKUPVALUE( [value_reported], [Date Column], MAXX(FILTER(ALL([Date Column]), [Date Column] < MAX([Date Column])), [Date Column]) ) ) )

This measure does the following:

1. It first checks if there's a value for the current cell.
2. If there is, it returns that value.
3. If not, it uses COALESCE to return the first non-blank value from: a. The next available future value (using LOOKUPVALUE and MINX) b. The most recent past value (using LOOKUPVALUE and MAXX)

To use this measure:

1. Add it to your matrix visual.
2. Make sure your matrix is using the date column you created in step 1 for the columns.
3. Use the fa_dp_jp field for the rows.

This solution will fill in all gaps with either the next or previous available value, prioritizing the next available value if both exist.

Remember to adjust the field names ([value_reported], [Date Column], etc.) to match your actual column names in the dataset.

This approach is flexible and will work even if there are multiple consecutive blank months, as it will keep looking forward or backward until it finds a non-blank value.