cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Frequent Visitor

Dynamic filter for visual based on a measure

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

2 REPLIES 2
Super User III
Super User III

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.

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. 

Helpful resources

Announcements
secondImage

Congratulations!

We are excited to announce the Power BI Super Users!

Microsoft Ignite

Microsoft Ignite with Arun Ulag

Featured Session: Drive Data Culture with Power BI- Vision, Strategy & Roadmap. Register here https://myignite.microsoft.com #MSIgnite #PowerPlatform #Power BI ​

Microsoft Ignite

Microsoft Ignite

Join digitally, March 2–4, 2021 to explore new tech that's ready to implement. Experience the keynote in mixed reality through AltspaceVR!

secondImage

The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.