Hello all. I am quite new to Power BI and DAX. I've been browsing the forum for an answer to my scenario, and have not been able to find one (although most likely I should have). My scenario is this: we have a management fee which is calculated on a monthly basis using a percentage of headcount in different offices. Example: allocatable costs are in total 100 in month 1, of which office A has paid 30 and office B has paid 70. Headcount is 40% in office A and 60% in office B. In such a case the management fee (net share of costs less costs already paid) would be 10 to be paid by A in month 1.
Now, I have been able to create a measure which calculates the management fee correctly for the selected month. However I do not understand how to calculate this year-to-date. The calculation needs to take place on a monthly basis i.e. YTD needs to be sum of January, February, etc. instead of calculated from YTD allocatable cost because headcount and costs vary from month to month. How do I tell the measure to calculate YTD so that January costs are allocated based on January headcount, February costs based on February headcount, etc. Does this make sense? I will paste my measure snippet here so perhaps you get a better understanding of what I mean. In the below code GL Natural=9600 is where I am deriving the headcount percentage, and AlreadyPaid works because I have forced a selection of a single office in the report filters.
ManagementFeeMeasure =
VAR currentMonth =
MONTH (max(DW_DimDate[FullDate]))
VAR currentYear =
YEAR (max(DW_DimDate[FullDate]))
VAR NetShare =
calculate(
sum(DW_FactValues[FactValue]);DW_FactValues[Type]="Actual";DW_GLType[GLTypeName]<>"Headcount";all(DimGLOfficeGrouping[Country]);filter(all(dW_DimDate[FullDate]);month(DW_DimDate[FullDate]) = currentMonth && YEAR(DW_DimDate[FullDate]) = currentYear)
) *
divide(
CALCULATE(
SUM (DW_FactValues[FactValue]); DimGLNatGroupingMF[GL Natural]=9600;filter(all(DW_DimDate[FullDate]);month(DW_DimDate[FullDate]) = currentMonth && YEAR(DW_DimDate[FullDate]) = currentYear))
;100
)
VAR AlreadyPaid =
CALCULATE (
SUM (DW_FactValues[FactValue]);
FILTER (
ALL (DW_DimDate);
MONTH (DW_DimDate[FullDate]) = currentMonth
&& YEAR (DW_DimDate[FullDate]) = currentYear
);
DW_FactValues[Type]="Actual";DW_GLType[GLTypeName]<>"Headcount"
)
RETURN
NetShare - AlreadyPaid
Any help greatly appreciated!
