Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
I am trying to prorate each car expense within a selected date range by the date-matching business portions of the selected businesses and I'm at my wits ends of how to do this in DAX, since between the car expenses table and the table that tracks the annual business portion distributions there is no physical relationship.
End goal: I want to create a measure CarExpenses[BusinessProratedCost] that will automatically prorate car expenses for any date range and any set of businesses I select, using date-range bound business portion values specified in a look-up table.
To make this concrete, let's say I have the following four tables:
ExpenseDate Cost
2/1/2017 $100
2/1/2018 $50
Note these expenses are not business specific - they are shared across all businesses, hence date range + business(es) proration.
StartDate EndDate Business BusinessPortion
1/1/2017 12/31/2017 A 20%
1/1/2017 12/31/2017 B 10%
1/1/2018 12/31/2018 A 10%
1/1/2018 12/31/2018 B 20%
Business
A
B
And in the model I have the following physical relations connecting them:
Now what I want to see is the following measure:
CarExpenses[BusinessProratedCost] = SUMX(CarExpenses, CarExpenses[Cost]*FilteredCarBusinessUsage[BusinessPortion])
The complexity is in how the lookup table needs to get filtered dynamically for every CarExpenses row by CarExpenses[ExpenseDate], prefiltered by the Date range slicer and the Businesses slicer.
Things get complex because my expenses can span multiple different date ranges in the CarBusinessUsage table and I can select multiple busineses.
Here are a few examples of expected outputs:
CarBusinessCostPortion = $100*20% = $20
CarBusinessCostPortion = $50*10% = $5
CarBusinessCostPortion = $100*20% + $50*10% = $25
CarBusinessCostPortion = $100*10% = $10
CarBusinessCostPortion = $100*(20% + 10%) = $30
CarBusinessCostPortion = $50*(10% + 20%) = $15
CarBusinessCostPortion = $100*(20% + 10%) + $50*(10% + 20%) = $45
In imperative pseudo-code that's what I'd envision:
QueryContext_SelectedBusinessesCarBusinessUsage = FILTER(CarBusinessUsage, <Business slicer>);
{
RowContext_FilteredCarBusinessUsage =
FILTER(QueryContext_DateRangeFilteredCarExpenses[ExpenseDate] >= (QueryContext_SelectedBusinessesCarBusinessUsage[StartDate]
&& QueryContext_DateRangeFilteredCarExpenses[ExpenseDate] <= (QueryContext_SelectedBusinessesCarBusinessUsage[EndDate]);
[BusinessProratedCost] += QueryContext_DateRangeFilteredCarExpenses[Cost] * SUM(RowContext_FilteredCarBusinessUsage[BusinessPortion];
}
but I can't seem to fit this into DAX no matter which pattern I tried to go with. I've spent an evening reading and trying different things and got nowhere, so it's time to ask the gurus. Here's how I've expressed above two iterators so far:
CarBusinessPortionByYear =
SUMX(CarExpenses,
CarExpenses[Cost]*SUMX(FILTER(CarUsageByBusiness,
CarExpenses[Expense date] >= CarUsageByBusiness[Start date]
&& CarExpenses[Expense date] <= CarUsageByBusiness[End date]),
CarUsageByBusiness[BusinessPortion]))
It gives me the total sum (same as SUM(CarExpenses[Cost])) and doesn't seem to filter by Business, even though my understanding is that that filter context applies through relationships and I do have a Businesses[Business] 1:* CarUsageByBusiness[Business] relationship set up in the model.
So, what is the magic query to prorate a sum over a column by row using a look-up proration that is matched on a row context date range filter and a query context dimension table slicer?
Thanks, Michael
Solved! Go to Solution.
HI @micwebnet,
You can try to use below formula to achieve your requirement:
Measure = VAR _business = ALLSELECTED ( CarBusinessUsage[Business] ) VAR temp = SUMMARIZE ( FILTER ( CarExpenses, [ExpenseDate] IN ALLSELECTED ( DateTable[Date] ) ), [ExpenseDate], [Cost], "Total ratio", SUMX ( FILTER ( ALLSELECTED ( CarBusinessUsage ), [ExpenseDate] IN CALENDAR ( [StartDate], [EndDate] ) && [Business] IN _business ), [BusinessPortion] ) ) RETURN SUMX ( temp, [Total ratio] * [Cost] )
Notice: I add a datetable as the source of slicer, business slicer is used the business column from 'carexpensesusage' table.
Result:
Regards,
Xiaoxin Sheng
Hi,
You may download my PBI file from here.
Hope this helps.
HI @micwebnet,
You can try to use below formula to achieve your requirement:
Measure = VAR _business = ALLSELECTED ( CarBusinessUsage[Business] ) VAR temp = SUMMARIZE ( FILTER ( CarExpenses, [ExpenseDate] IN ALLSELECTED ( DateTable[Date] ) ), [ExpenseDate], [Cost], "Total ratio", SUMX ( FILTER ( ALLSELECTED ( CarBusinessUsage ), [ExpenseDate] IN CALENDAR ( [StartDate], [EndDate] ) && [Business] IN _business ), [BusinessPortion] ) ) RETURN SUMX ( temp, [Total ratio] * [Cost] )
Notice: I add a datetable as the source of slicer, business slicer is used the business column from 'carexpensesusage' table.
Result:
Regards,
Xiaoxin Sheng
User | Count |
---|---|
141 | |
113 | |
104 | |
77 | |
64 |
User | Count |
---|---|
135 | |
123 | |
101 | |
71 | |
61 |