Market rent table filling in blank dates with previous values

[sstotter]

I have a data source table which is a record of all rent changes from month to month per unit of a building. If a paticular unit doesn't have a change in a month there is no entry. When i put that in a visual which has a relationship with a Date table i get blank values in the months that dont have entries because there was no change. 

I need to fill in the blank months with the last non blank value, which would be the last market rent change.

I've included two pictures of the data table and visual with blank values.

Thanks.  Data table

Visual with blank month which i need to show previous non blank value

[v-piga-msft]

Hi @sstotter，

If I understand your scenario correctly, you could create two measures to get the result you want.

1. Create the  Total Rent measure (My table is Sheet2, you could rename it as yours.):

Total Rent = 
CALCULATE(MAX( Sheet2[Market rent] ),
FILTER(Sheet2,YEAR(Sheet2[Date of Change])=YEAR(MAX('Date'[Date]))
        &&MONTH(Sheet2[Date of Change])=MONTH(MAX('Date'[Date]))))

 2. Create the measure(you could rename the measure as you want):

Measure = IF (
    [Total Rent] > 0,
    [Total Rent],
    VAR previousDate =
        CALCULATE (
            MAX ( 'Sheet2'[Date of Change] ),
            FILTER(Sheet2,Sheet2[Date of Change]<MAX('Date'[Date])&&Sheet2[Market rent]>0)
        )
    RETURN
        CALCULATE (
            MAX( Sheet2[Market rent] ),
            FILTER ( ALLSELECTED(Sheet2), 'Sheet2'[Date of Change] = previousDate && Sheet2[Unit]=MAX(Sheet2[Unit]))
        ) 
)

The picture of the result is below:

For more details, you could refer to my test pbix.file.

Something you should note that you should not create the relationship of the Sheet2 table and the Date table.

Hope it can help you!

Best Regards,

Cherry  

[sstotter]

Hi Cherry, 

Thank you for your detailed response, I really appreciate it.

The solution seems to work perfectly on a per unit level but if i need to see the data consolidated on a property level, the totals dont show up for the months with no data, as well, the totals for rows and columns are the max, whereas I need them to be averages.

Is there any way to fix that? Eventually i would like to roll up the visual dynamically from unit to property to regional level. See below picture with blank totals. If i am to drill-up on rows, i get the property 107r with blanks in a number of months. I've also included a screenshot of a pivot table which with shows the desired output for the totals. 

Desired totals output...per unit averages.

Thanks,

Steve

[v-piga-msft]

Hi @sstotter,

From your description,  

---

Is there any way to fix that? Eventually i would like to roll up the visual dynamically from unit to property to regional level. See below picture with blank totals. If i am to drill-up on rows, i get the property 107r with blanks in a number of months. I've also included a screenshot of a pivot table which with shows the desired output for the totals. 

---

The solution seems to work perfectly on a per unit level but if i need to see the data consolidated on a property level, the totals dont show up for the months with no data, as well, the totals for rows and columns are the max, whereas I need them to be averages.

1.If you need the Totals to be Average, you could modify the measure as :

Total Rent =
CALCULATE (
    AVERAGE ( 'table'[Market rent] ),
    FILTER (
        'table',
        YEAR ( 'table'[Date of Change] ) = YEAR ( MAX ( 'Date'[Date] ) )
            && MONTH ( 'table'[Date of Change] ) = MONTH ( MAX ( 'Date'[Date] ) )
    )
)

2.By my test with the new data you provide, the measure also worked. You could refer to the picture below.

For the details, you could refer to my new test pbix file.

If the measure still doesn’t work in your scenario, could you share a dummy pbix file which can reproduce the issue, so that we can help further investigate on it? You can upload it to OneDrive or Dropbox and post the link here. Do mask sensitive data before uploading.)

Best Regards,

Cherry