How to deal with missing data (and find last known value) in calculated column?

[Spartanos]

I would like to add data in a calculated column if the data for that month is blank. The values are gathered from DAX formulas.

The ideal situation would be to get the last known value, if month 1=100, month 2= blank and month 3= 120, by calculating the average of the last known value, which is then 110.

If there are more than two month blanks, I would like to take the average of the last two known values as well, the month before the blank and the month after the blank. If there is a blank value at the end, for first X in 2022-05, I don't want to add additional values.

https://drive.google.com/file/d/18cVJ4Blap07_1SFWTwnDHvLGUG2wrZZG/view?usp=sharing

[lbendlin · Accepted solution]

see attached

[Spartanos]

I am sorry, I wasn't clear. Column Y is a seperate column, and for that column I don't have any missing values. And in order to plot the data for both columns, I would like to add data for the missing rows in column First X. 

[lbendlin · Accepted solution]

see attached

[Spartanos]

I tried to use your calculations in the data, but I think I have a issue doing so because the orginal datset columns are DAX meassures. The DAX measure is telling me that may not be used in this measure. 

Could that have an impact on your DAX measure(s)?

[lbendlin]

Please provide sanitized sample data that fully covers your issue. You may have to post a sample version of your pbix for that.

Please show the expected outcome based on the sample data you provided.

[Spartanos]

Here is the link of the updated pbix: https://drive.google.com/file/d/1IGfN5HoVzt7tpAOZ66wtuWYepZoVU3dS/view?usp=sharing

You will see that average_Y has missing data, and I want to solve that by picking the average of the value before and above. Hence, for 2021-02 I would like to see (25+56/2)= 40,5.

Or you could use your a geometric intrapolation to fill the blanks in average_Y. Thank in advance for your help!

[lbendlin · Accepted solution]

Your data model is missing the Dates/Calendar table.  Whitespace reporting requires disconnected dimensions.  You cannot report on something that isn't there unless you have that independent "ruler" *

- Add a Dates table

- Disconnect Table_X from Table_Y

- Cleanup Table_Y (remove all rows that don't have a value)

- recreate your visuals based on the calendar table dates and new measures that work similar to what I proposed in my first example.

*) In your specific case you might actually be able to connect the Dates dimension to both fact tables and get away with it.  Depends on what else is happening in your data model.

Attached is a "no code"  example.

[Spartanos]

Is there a way to solve this with the current data model?

[lbendlin]

No, that data model is unsuitable for the scenario.

[Spartanos]

Thanks a lot, unfortunately, I don't understand your calculations. How did you come up with the values of 2.155 for 2019-02, and 1.738 for 2020-02?

[lbendlin]

I applied a geometric intrapolation.  Think of it as a line between the values on each side of the gap. The slope is then applied to each gap member. For single item gaps this is equivalent to taking the average.

[lbendlin]

It is not clear what you are trying to achieve. Is column Y the expected outcome?