cancel
Showing results for
Did you mean:
Highlighted
Helper I

## Daily rate calculation filtered by customer

Hi,

I have a rental business and we are tracking the daily earnings based on on/off hire dates.  I have this working OK having created a calendar table and the following column statement:

DailyEarnings = CALCULATE(SUM('ContItems'[DailyRate]),FILTER(ContItems,EARLIER('Calendar'[Date])>=ContItems[HIREDATE]&&EARLIER('Calendar'[Date])<=ContItems[EndDate]]&&ContItems[ACCT]="CUSTOMER 1")

I added the check for "CUSTOMER 1" to work out the daily rate for a specific customer - all good.   Problem is I have 100's of customers and I don't want to have a column entry for each, and would rather use a slicer on a visual to choose which customer I want to find the daily rate for.

How can I use a customer selection slicer and cause the table to re-fresh?

Thanks
1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Super User IV

You are going to need to convert that to a measure.

---------------------------------------

##### I have a NEW book! DAX Cookbook from Packt
Over 120 DAX Recipes!

Proud to be a Super User!

3 REPLIES 3
Highlighted
Super User IV

You are going to need to convert that to a measure.

---------------------------------------

##### I have a NEW book! DAX Cookbook from Packt
Over 120 DAX Recipes!

Proud to be a Super User!

Highlighted
Helper I

Hi,

Could you explain a little further.   The calendar tabke does not have the account names in it, they are in another table.

How would that measure statement look - thanks

Highlighted
Community Support

I'm not certain what's the structure of dataset look like but maybe you can try to create a measure not a column like this and set a customer and date slicer to filter:

``````Earnings =
CALCULATE (
SUM ( Contltems[DailyRate] ),
FILTER (
'Contltems',
SELECTEDVALUE ( 'Calendar'[Date] ) >= 'Contltems'[HIREDATE]
&& SELECTEDVALUE ( Contltems[EndDate] ) <= 'Contltems'[EndDate]
),
ALL ( 'Contltems' )
)``````

If not help, could you please share a dummy pbix file as a sample for further discussion? Sample data and expected output would help tremendously.

Best Regards,
Yingjie Li

If this post helps then please consider Accept it as the solution to help the other members find it more quickly.

Announcements

#### Power Platform Community Conference

Check out the on demand sessions that are available now!

#### Microsoft Power Platform Communities

Check out the Winners!

#### Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Top Solution Authors
Top Kudoed Authors