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 there, I need help with a calculation that involves two tables.
My main table has targets indexed by month, and for simplicity I want to calculate the working days; the reference table is a Calendar table which is indexed by day, contains the month, and has another column if the day is a working day or not.
Target: - month - working_days ? Calendar - date - month - is_working_num
What I have tried is to use calculate to do:
working_days = CALCULATE( sum(Calendar[is_working_num]), Calendar[month] = Target[month] )
I get an error that the expression contains multiple columns, but I can only use one.
Yet it seems like I'm close, but I have tried for a while with no success.
I should also mention that there is not a relation via month-month. This is another issue, that I might address in another time.
Thanks.
Solved! Go to Solution.
@Diego-mx,
Please check if one of the following columns return your expected result. For more details, please review attached PBIX file.
Column= CALCULATE( sum(Calendar[is_working_num]), FILTER('Calendar','Calendar'[Monthnumber] = Target[month] ))
working_days = SUMX( FILTER('Calendar','Calendar'[Monthnumber]= Target[month]),'Calendar'[is_working_num] )
Regards,
Lydia
@Diego-mx,
Please check if one of the following columns return your expected result. For more details, please review attached PBIX file.
Column= CALCULATE( sum(Calendar[is_working_num]), FILTER('Calendar','Calendar'[Monthnumber] = Target[month] ))
working_days = SUMX( FILTER('Calendar','Calendar'[Monthnumber]= Target[month]),'Calendar'[is_working_num] )
Regards,
Lydia
Read my article here. I realise it is not identical, but it should help you. https://powerpivotpro.com/2016/12/how-many-working-days-has-an-employee-been-off-work/
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 |
---|---|
118 | |
104 | |
77 | |
73 | |
52 |
User | Count |
---|---|
145 | |
109 | |
109 | |
90 | |
64 |