The ultimate Microsoft Fabric, Power BI, Azure AI, and SQL learning event! Join us in Stockholm, Sweden from September 24-27, 2024.
2-for-1 sale on June 20 only!
Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started
Hi Guys,
I've been tinkering with Power BI for a little while now, and finally gotten my first assignment at work.
Unfortunately i can't wrap my head around a solution to the request i've gotten.
Success criteria:
main issue:
if a project have been running for more then 3 months, i need to show the hours used before the start of the 3 month-period.
this is currently not possible, because i'm using a filter to show the last 3 months in my matrix.
This is a minimized replica of the data i'm working with
I hope one of you can give me a hint or two.
Thank you in advance.
Solved! Go to Solution.
Hi @Anonymous
The data you provided is a bit complicated and not easy to create. I created a sample, you can refer to this method.
Original data :
(1)First get the maximum date and minimum date of the project .
(2)Use MONTH dax to return the number for date and compare the diff between max date and min date .IF the diff >3 ,then sum the hours before 3 months ago , or return 0 .
worked +3 months ago =
var max_date=CALCULATE(MAX('Table'[Date]),ALLEXCEPT('Table','Table'[Project]))
var min_date=CALCULATE(MIN('Table'[Date]),ALLEXCEPT('Table','Table'[Project]))
var sum_hours = CALCULATE(SUM('Table'[Hours]),FILTER(ALLEXCEPT('Table','Table'[Project]),'Table'[Date]<EOMONTH( CALCULATE(MAX('Table'[Date]),ALLEXCEPT('Table','Table'[Project])),-3)+1))
return
IF(MONTH(max_date)-MONTH(min_date)>3,sum_hours,0)
The effect is as shown:
You can use Matrix visual to display the result . Note :measure can only put in Values ,and then it will display in each column ,you can hide them just keep the total one .
I have attached my pbix file ,you can refer to it .
Best Regards
Community Support Team _ Ailsa Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @Anonymous
Has your problem been solved ? If it has been solved, provide your method for the problem then consider Accept it as the solution to help the other members find it more quickly.
Best Regards
Community Support Team _ Ailsa Tao
Hi @Anonymous
The data you provided is a bit complicated and not easy to create. I created a sample, you can refer to this method.
Original data :
(1)First get the maximum date and minimum date of the project .
(2)Use MONTH dax to return the number for date and compare the diff between max date and min date .IF the diff >3 ,then sum the hours before 3 months ago , or return 0 .
worked +3 months ago =
var max_date=CALCULATE(MAX('Table'[Date]),ALLEXCEPT('Table','Table'[Project]))
var min_date=CALCULATE(MIN('Table'[Date]),ALLEXCEPT('Table','Table'[Project]))
var sum_hours = CALCULATE(SUM('Table'[Hours]),FILTER(ALLEXCEPT('Table','Table'[Project]),'Table'[Date]<EOMONTH( CALCULATE(MAX('Table'[Date]),ALLEXCEPT('Table','Table'[Project])),-3)+1))
return
IF(MONTH(max_date)-MONTH(min_date)>3,sum_hours,0)
The effect is as shown:
You can use Matrix visual to display the result . Note :measure can only put in Values ,and then it will display in each column ,you can hide them just keep the total one .
I have attached my pbix file ,you can refer to it .
Best Regards
Community Support Team _ Ailsa Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @v-yetao1-msft ,
I managed to get it to work with the help of your DAX.
I had to change the values to the appropiate data, but it seems to be working now.
Thank you for your help!
Hi @Anonymous
(1) Only show Hourly_fee in the totals and NOT under every month ,you can turn off Word wrap in both Values and Column headers .
(2)You said that if the project has been running for more than 3 months , you need to show the hours used before the start of the 3 month-period . In your sample , what is your start date ?
For example , the project “extend service” has a min date 2021/02/02 , so you need sum the hours before 2021/02/02 ?
You can create a measure like:
Measure = CALCULATE(SUM(task[hours_worked]),FILTER(ALLEXCEPT(project,project[project_name]),SELECTEDVALUE(task[date_worked])<MIN(task[date_worked])))
Best Regards
Community Support Team _ Ailsa Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hello @v-yetao1-msft,
(1) i'm already doing this, and since you also suggested it, i'm gonna continue using this -Thank you
(2) You said that if the project has been running for more than 3 months , you need to show the hours used before the start of the 3 month-period . In your sample , what is your start date ?
Unfortunately i totally forgot to add the start date, but it would be project[start_date]
For example , the project “extend service” has a min date 2021/02/02 , so you need sum the hours before 2021/02/02 ?
I'm sorry for not writing my wishes correctly.
A project can run for lets say 4 months.
my matrix (should) only shows the last 3 named months.
i'm hoping that it would be possible to:
have a column before the months with all hours worked before the shown 3 months.
Something similar to the picture below
i hope that makes sense and thank you!
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
Check out the June 2024 Power BI update to learn about new features.
User | Count |
---|---|
99 | |
93 | |
83 | |
64 | |
58 |
User | Count |
---|---|
248 | |
123 | |
112 | |
79 | |
78 |