cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Helper I
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
Super User IV

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

 

Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490


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

@ me in replies or I'll lose your thread!!!

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




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




View solution in original post

3 REPLIES 3
Highlighted
Super User IV
Super User IV

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

 

Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490


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

@ me in replies or I'll lose your thread!!!

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




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




View solution in original post

Highlighted

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

Hi @waynewoodhead ,

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.
Please see this post regarding How to Get Your Question Answered Quickly:
https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

 

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.

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

Community Conference

Microsoft Power Platform Communities

Check out the Winners!

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Top Solution Authors
Top Kudoed Authors