Daily rate calculation filtered by customer

[waynewoodhead]

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

[Greg_Deckler · Accepted solution]

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

[waynewoodhead]

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

[v-yingjl]

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.