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:   Da...
Need Help
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.