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.
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.
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
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.
Thanks,
Steve
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
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 |
---|---|
105 | |
96 | |
79 | |
66 | |
62 |
User | Count |
---|---|
145 | |
113 | |
105 | |
85 | |
65 |