Forum Discussion

waynewoodhead's avatar
waynewoodhead
Icon for Helper I rankHelper I
6 years ago
Solved

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
Need Help

3 Replies

    • waynewoodhead's avatar
      waynewoodhead
      Icon for Helper I rankHelper I
      6 years ago

      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's avatar
        v-yingjl
        Icon for Community Support rankCommunity Support
        6 years ago

        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.