Dynamic filter for visual based on a measure

[ttopal]

Hi all,

I have electricity invoice data for number of buildings.

I am filtering on building selection to show consumption for the last 13 months against the average of all buildings.

The issue is some of the buildings are running behind on invoices, say for average i have data till december while for some buildings the last invoice date can be anything between september to december.

So I would like to adjust that 13 months period for each building.

I am using a measure to get the last invoice data for each building as:

Last Invoice Date = LASTNONBLANK('Grid'[Date],Sum(Grid[Consumption]))

Then to filter the visual to not show the dates after the last invoice I use:

IF(MIN('Calendar'[Date]) <= [Last Invoice Date],_ave,BLANK())

Which works. Then I create another measure to get the starting date for a visual:

First Invoice Date for Visual = DATEADD(LASTNONBLANK(Grid[Date],SUM(Grid[Consumption])),-13,MONTH)

When I use the same method to filter the visual it doesn't work. It returns all dates for the visual.

Is there another approach I can use?

Thanks

[lbendlin]

Think about what you mean when you say "average of all buildings" . You can approach this in many different ways

- Sum of invoice value across all buildings divided by number of invoices

- average of (average of invoice values for each building based on that building's invoice sum divided by this building's count of invoices)

- monthly averages across all buildings that have invoices for that month

etc etc.

Once you decided how to calculate it the actual implementation will be straightforward.

[ttopal]

Thanks for the reply, but I dont think I have asked my quesiton properly. My average of buildings is monthly averages across all buildings that have invoices for that month. So there will always be a value for every month. The problem is more about the buildings that doesnt have the invoices yet. So basically this question is more about limiting the x axis by shifting the 13 months window based on the recieved invoices for a specific building. Then the average consumption should just be added on top of it.