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.
Hello,
I am looking to create a count of number of completed jobs and forecasted jobs within each month of the year based on the following columns and be able to filter by complexity, industry, tool type etc. Can you help me out with the formula please?
Job name | Complexity | Industry | Tool | Start date | Finish date |
Thanks,
Gordon
Solved! Go to Solution.
Hi @G0ggs ,
Based on your description, you could create some measures as follows.
Here is my test table.
1. create a calendar table
2. create a measure
completed jobs =
CALCULATE (
DISTINCTCOUNT ( 'Test'[Job name] ),
ALLEXCEPT ( 'Test', Test[Complexity], Test[Industry], Test[Tool] ),
FILTER (
'Test',
YEAR ( MAX ( 'Test'[Finish date] ) ) = YEAR ( MAX ( 'calendar'[Date] ) )
&& MONTH ( MAX ( 'Test'[Finish date] ) ) = MONTH ( MAX ( 'calendar'[Date] ) )
)
)
Result:
Hope that's what you were looking for.
Best Regards,
Yuna
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @G0ggs ,
Based on your description, you could create some measures as follows.
Here is my test table.
1. create a calendar table
2. create a measure
completed jobs =
CALCULATE (
DISTINCTCOUNT ( 'Test'[Job name] ),
ALLEXCEPT ( 'Test', Test[Complexity], Test[Industry], Test[Tool] ),
FILTER (
'Test',
YEAR ( MAX ( 'Test'[Finish date] ) ) = YEAR ( MAX ( 'calendar'[Date] ) )
&& MONTH ( MAX ( 'Test'[Finish date] ) ) = MONTH ( MAX ( 'calendar'[Date] ) )
)
)
Result:
Hope that's what you were looking for.
Best Regards,
Yuna
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi Yuna thanks for this!
I was wondering if it also possible to create a target table for each tool type so when plotted you can see the completed jobs per month versus the target please?
Thanks
Hi @G0ggs ,
Based on your description, maybe you could do some steps as follow.
1. create a target table. Here is my test table.
2. establish a relationship between two tables.
3. create a "KPI" visual.
Result:
Hope that's what you were looking for.
Best Regards,
Yuna
Hello Yuaj I created a date table then linked many to 1 single but I get an error as shown below, not sure if I have set up the date correctly?
interesting! How did you create the columns in the date table it only shows 1 formula for the date column?
Thanks,
Gordon
@G0ggs , this seems very similar to HR blog.
refer
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 |
---|---|
116 | |
100 | |
69 | |
68 | |
44 |
User | Count |
---|---|
145 | |
106 | |
105 | |
90 | |
65 |