Help with SUM using a measures and 'bins'

[ejoneslor]

I'm a pretty new poster here so apologies if I've not posted or given examples in the right way.

I'm looking for some help with my measure to have my charts show correctly. I will give examples as best I can but it is commerically sensitive data so can't share the actual values.

I have a dataset showing hires, daily hire rates and the start and end date of the hire in question. I am trying to add up the total value of all of the daily hire rates that are active in a period. See a simplified example below.

Hire              Daily Rate      Hire Start         Hire End

Project 1       £100              6th March       19th March

Project 2       £150              13th March      26th March

These are 2nr, 2 week hires with the middle week overlapping. So I would want the following results using a daily and then a weekly 'bin' on my report

Date               Daily Total

6th March      £100

7th March      £100

8th March      £100

9th March      £100

10th March    £100

11th March    £100

12th March    £100

13th March    £250

14th March    £250

15th March    £250

16th March    £250

17th March    £250

18th March    £250

19th March    £250

20th March    £150

21st March    £150

22nd March    £150

23rd March    £150

24th March    £150

25th March    £150

26th March    £150

w/c                  Weekly Total

6th March       £700

13th March     £1,750

20th March     £1,050

If I use a claulated column in my Date table then these calculations work but then they are not compatible with any slicers. If I use a measure in the same table then they just don't add up correctly and the value of my 'bin' whether I set to days, weeks, months or years gives me pretty much the same total which I 'think' is an average rather than a total sum.

My measure is set as below. Can anyone see my error please?

 

MeasHireWon =

VAR A =

    MAX('Date'[Date])

RETURN

    CALCULATE (

        SUM ( 'Opportunities'[DailyHireRate] ),

        ALLSELECTED ( 'Opportunities'[Name] ),

        'Opportunities'[Product_On_Site_Date__c] <= A,

        'Opportunities'[Product_Off_Site_Date__c] >= A,

        'Opportunities'[StageName] = "Closed Won",

        'Opportunities'[Contract_Type__c] IN {"Hire","Sale and Hire"},

        'Opportunities'[Included_in_Pipeline_Reports__c] = TRUE

    )

[Jihwan_Kim · Accepted solution]

Hi,

I am not sure how your datamodel looks like, but I tried to create a sample pbix file like below.

Please check the below picture and the attached pbix file.

I hope the below can provide some ideas on how to create a solution for your datamodel.

Daily total: =
SUMX (
    DISTINCT ( 'Calendar'[Date] ),
    CALCULATE (
        SUMX (
            FILTER (
                Project,
                Project[Hire start] <= MAX ( 'Calendar'[Date] )
                    && Project[Hire end] >= MIN ( 'Calendar'[Date] )
            ),
            Project[Daily rate]
        )
    )
)

[ejoneslor]

@Jihwan_Kim Thats Awesome, thank you. It works perfectly once tweaked for my column names.
You've saved my sanity.