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.
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.
Solved! Go to 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
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
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.
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())
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?
Disconnecting the date table was the solution. Your periodic billing formula worked. Many thanks for your suggestion!
Awesome! Glad I could help!
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 |
---|---|
106 | |
93 | |
75 | |
62 | |
50 |
User | Count |
---|---|
147 | |
107 | |
105 | |
87 | |
61 |