Create a measure that can be filtered

[Anonymous]

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

Client	Product	Product Type	Monthly hours	Start date	End date
Client A	Online Advertising	Advertising	4	01/01/2018	04/01/2018
Client B	Online Advertising	Advertising	5	01/01/2018	01/01/2020
Client C	Website Maintenance	Service	5	03/01/2018	01/01/2020
Client D	Online Advertising	Advertising	3	04/01/2018	01/01/2020
Client D	Website Maintenance	Service	2	09/01/2018	01/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.

[Greg_Deckler · Accepted solution]

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

[Anonymous]

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:

Client	Monthly hours	Start date	End date
Client A	4	01/01/2018	03/31/2018

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

Hope this clarifies.

[Greg_Deckler]

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())

[Anonymous]

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?

[Anonymous]

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

[Greg_Deckler]

Awesome! Glad I could help!