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

Create a measure that can be filtered

Hi guys, I have a question. I want to create an insight using a table like this (in a far more complex model):

 

ClientProductProduct TypeMonthly hoursStart dateEnd date
Client AOnline AdvertisingAdvertising401/01/201804/01/2018
Client BOnline AdvertisingAdvertising501/01/201801/01/2020
Client CWebsite MaintenanceService503/01/201801/01/2020
Client DOnline AdvertisingAdvertising304/01/201801/01/2020
Client DWebsite MaintenanceService209/01/201801/01/2020

 

I want to create a visual, preferably a column graph, that shows me the total monthly hours each month for selected dates (by slicer). I also have a Client key table with a slicer, so I want to be able to filter this graph based on client, so that clicking the Client slicer shows me the monthly hours for only this client. A client can have multiple products, and they can change between months.

 

I have a hard time getting there, because the data is using start and end dates, and so when plotting total monthly hours the model only shows them in the month where they start (because start date is linked to a calender date). I somehow need to make the model understand that these monthly hours return every month between the start and end date. They can change between months (a client can choose to buy more products) and I only want to be able to visualize this BY MONTH. 

 

I probably need to create a table within the measure and calculate my totals there, so it can be filtered on client. But I'm not sure how to get there.

1 ACCEPTED SOLUTION

Accepted Solutions
Super User IV
Super User IV

Re: Create a measure that can be filtered


Take a look at these two Quick Measures as I think you want something like them.

https://community.powerbi.com/t5/Quick-Measures-Gallery/Open-Tickets/m-p/409364
https://community.powerbi.com/t5/Quick-Measures-Gallery/Periodic-Billing/m-p/409365


---------------------------------------

Not the Power BI thought police...

I have NEW book! 
DAX Cookbook from Packt
Over 120 DAX Recipes!
Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

View solution in original post

6 REPLIES 6
Super User IV
Super User IV

Re: Create a measure that can be filtered


Take a look at these two Quick Measures as I think you want something like them.

https://community.powerbi.com/t5/Quick-Measures-Gallery/Open-Tickets/m-p/409364
https://community.powerbi.com/t5/Quick-Measures-Gallery/Periodic-Billing/m-p/409365


---------------------------------------

Not the Power BI thought police...

I have NEW book! 
DAX Cookbook from Packt
Over 120 DAX Recipes!
Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

View solution in original post

AdriaanMultiply
Frequent Visitor

Re: Create a measure that can be filtered

Hi Greg,

 

Many thanks for your suggestion. I wrote a measure similar to your periodic billing suggestion. It still only counts the monthly hours in their first month, but they are recurring hours. So to simplify my question; 

 

Client buys 10 hours each month starting february 1st. They stop buying hours before the start of april. The data only contains:

  

ClientMonthly hoursStart dateEnd date
Client A401/01/201803/31/2018

 

Total amount of hours bought is 12 (4 hours for jan, feb and march) and I want my visual to show only when I set my date selector on february or march.

 

Hope this clarifies.

Super User IV
Super User IV

Re: Create a measure that can be filtered

Assuming a disconnected date table, you probably want something along the lines of:

 

Measure = 
VAR __month = MAX('Calendar'[Month])
VAR __start = MONTH(MAX('Table'[Start Date]))
VAR __end = MONTH(MAX('Table'[End Date]))
RETURN
IF(__month >= __start && __month <= __end,MAX('Table'[Monthly hours]),BLANK())

---------------------------------------

Not the Power BI thought police...

I have NEW book! 
DAX Cookbook from Packt
Over 120 DAX Recipes!
Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

AdriaanMultiply
Frequent Visitor

Re: Create a measure that can be filtered

Thanks again, I will try this later. Why do I need to use a disconnected date table? This means that there must be no relationship between the date table and the data table I'm trying to analyze, correct?

AdriaanMultiply
Frequent Visitor

Re: Create a measure that can be filtered

Disconnecting the date table was the solution. Your periodic billing formula worked. Many thanks for your suggestion!

Super User IV
Super User IV

Re: Create a measure that can be filtered

Awesome! Glad I could help!


---------------------------------------

Not the Power BI thought police...

I have NEW book! 
DAX Cookbook from Packt
Over 120 DAX Recipes!
Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

Helpful resources

Announcements
New Ranks Launched March 24th!

New Ranks Launched March 24th!

The time has come: We are finally able to share more details on the brand-new ranks coming to the Power BI Community!

‘Better Together’ Contest Finalists Announced!

‘Better Together’ Contest Finalists Announced!

Congrats to the finalists of our ‘Better Together’-themed T-shirt design contest! Click for the top entries.

Arun 'Triple A' Event Video, Q&A, and Slides

Arun 'Triple A' Event Video, Q&A, and Slides

Missed the Arun 'Triple A' event or want to revisit it? We've got you covered! Check out the video, Q&A, and slides now.

Join THE global Microsoft Power Platform event series.

Join THE global Power Platform event series.

Attend for two days of expert-led learning and innovation on topics like AI and Analytics, powered by Dynamic Communities.

Community Summit North America

Community Summit North America

Innovate, Collaborate, Grow. The top training and networking event across the globe for Microsoft Business Applications

Top Solution Authors
Top Kudoed Authors