Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
micwebnet
Employee
Employee

Pro-rated sum, using filtered look-up table with row-context data range and query context slicer fil

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:

 

  • CarExpenses table that contains

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.

 

  •  CarBusinessUsage table that contains

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%

 

  • Businesses table that contains

Business

  A

  B

 

  • Dates dimension calculated table

 

And in the model I have the following physical relations connecting them:

 

  1. CarExpenses[ExpenseDate] is *:1 linked to Dates[Date]
  2. CarBusinessUsage[Business] is *:1 Businesses[Business]

 

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:

 

  1. Date range slicer = {1/1/2017, 12/31/17} and Business slicer = A

 

   CarBusinessCostPortion = $100*20% = $20

 

  1. Date range slicer = {1/1/2018, 12/31/18} and Business slicer = A

 

   CarBusinessCostPortion = $50*10% = $5

 

 

  1. Date range slicer = {1/1/2017, 12/31/18} and Business slicer = A

 

   CarBusinessCostPortion = $100*20% + $50*10% = $25

 

  1. Date range slicer = {1/1/2017, 12/31/17} and Business slicer = B

 

   CarBusinessCostPortion = $100*10% = $10

 

  1. Date range slicer = {1/1/2017, 12/31/17} and Business slicer = A & B

 

   CarBusinessCostPortion = $100*(20% + 10%) = $30

 

  1. Date range slicer = {3/1/2017, 12/31/18} and Business slicer = A & B

 

   CarBusinessCostPortion = $50*(10% + 20%) = $15

 

  1. Date range slicer = {1/1/2017, 12/31/18} and Business slicer = A & B

 

   CarBusinessCostPortion = $100*(20% + 10%) + $50*(10% + 20%) = $45

 

In imperative pseudo-code that's what I'd envision:

 

  • QueryContext_DateRangeFilteredCarExpenses = FILTER(CarExpenses, <Date range slicer>;

 

  • [BusinessProratedCost] = 0;

     QueryContext_SelectedBusinessesCarBusinessUsage = FILTER(CarBusinessUsage, <Business slicer>);

 

  • foreach QueryContext_FilteredCarExpenses[Cost]

     {

        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

1 ACCEPTED SOLUTION
v-shex-msft
Community Support
Community Support

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:

56.gif

 

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

View solution in original post

2 REPLIES 2
Ashish_Mathur
Super User
Super User

Hi,

 

You may download my PBI file from here.

 

Hope this helps.

 

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
v-shex-msft
Community Support
Community Support

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:

56.gif

 

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.