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 all,
I am having some troube converting a DAX calculated column to M.
It would be nice to have it for a matter of having it working in a better way (to do later appends, etc.).
The DAX calculated column is this:
Value =
VAR __WorkingDaysRow =
CALCULATE (
//The workday column is 1 or 0, according if it is a workday or not
SUM ( 'Date'[Workday] ),
DATESBETWEEN (
'Date'[Date],
MAX (
//the 'Demand - Facts'[Month] is always the first day of the month
'Demand - Facts'[Month],
'Demand - Facts'[From]
),
MIN (
NEXTMONTH ( 'Demand - Facts'[Month] ) - 1,
'Demand - Facts'[To]
)
)
)
VAR __WorkingDaysMonth =
CALCULATE (
SUM ( 'Date'[Workday] ),
DATESBETWEEN (
'Date'[Date],
'Demand - Facts'[Month],
NEXTMONTH ( 'Demand - Facts'[Month] ) - 1
)
)
RETURN
DIVIDE ( __WorkingDaysRow, __WorkingDaysMonth, 0 )*'Demand - Facts'[Value per Demand]
//Demand - Facts'[Value per Demand] is the FTE for each row/activity
Basically, I have some activites with a "From" and "To" date. I calculate for each month the number of working days and then calculate the FTEs per each month accordingly to the FTEs for the whole activity.
Now, I wanted to create this into M.
I already created a "Working days" Table in Power Query, with the correspondence of 0/1 for each working day.
So, basically, I wanted a formula in M that would:
Thanks for your help,
Pedro
Doesn't sound like a calculated column or M code is helpful here. What you can do is include a column in your calendar table that has working days per month, but that is something you should define outside of Power BI.
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 |
---|---|
110 | |
96 | |
77 | |
63 | |
55 |
User | Count |
---|---|
143 | |
109 | |
89 | |
84 | |
66 |