Value by Date Expression

[Daz4]

I have two tables and need to work out the cost per hour. I cannot join by date for various reasons.

I have a Table using Date2 as Dimension and sum(Time) as the expression and need to bring in the cost as a second expression. The following expression does not work as it brings in no values, can you tell me why? I have tried reversing the Dates and also removing the sum. 

=IF(DATE2=DATE1, sum(Cost))

Date1              Cost

1/01/2022      103 

2/01/2022      558

...

Date2              Time

1/01/2022      2

2/01/2022      6

...

[BrunPierre]

=Sum(If(Date2=Date1,Cost))

[Daz4]

Thank you that is exactly what I was looking for greatly appreciated. 

I wonder if you could also assist on a change to that formula to get a sum of a date range?

I now have a table grouped by Monthname(Date1)

The following doesn't work:

=sum(IF(Date2>=MIN(Date1) andDate2<=MAX(Date1),Cost))

[tresesco]

yes your expression would throw nested aggregation error. Try like:

=sum(IF(Date2>=MIN(Total Date1) and Date2<=MAX(Total Date1),Cost))

[Daz4]

Thanks for your time on this. 

It doesn't quite do what I want it to do as it returns the total for the entire date range as a single number summed up across the whole date range, not split into the months. So if Oct = $50k and Nov = $70, if I select one or the other they show correctly, but if I select both months then the same figure of $120k appear for each month. Any ideas how to get it working across each month?

Date    Cost

Oct      50000

Date    Cost

Nov     70000

Date    Cost

Oct       12000

Nov     12000

[tresesco]

One way could be like modifying the above expression like:

=sum(IF(Date2>=Aggr(MIN(Date1), MonthField) and Date2<=Aggr(MAX(Date1), MonthField),Cost))

Note: Use of aggr() would depend on where and how your are using this expression. I.e. - if you are using in chart, you might also need to include the chart dimensions in aggr() alongwith the month field.