Create a conditional sum using a date table

[Anonymous]

Hi

I am trying to create a dax measure, where if the date (taken from the date table) is between the start and end date then sum the cost.

I'm think i'm trying to combine too many steps in one. Is someone able to help with the query?

Thanks

Cost	Start Date	End date
10	01/01/2024	05/01/2024
20	08/01/2024	09/01/2024
20	10/01/2024	15/01/2024

Date table

01/01/2024

02/01/2024

03/01/2024

04/01/2024

05/01/2024

06/01/2024

07/01/2024

08/01/2024

[Anonymous · Accepted solution]

Hi @Anonymous ,

Your solution is great, bhanu_gautam. It worked like a charm! Here I have another idea in mind, and I would like to share it for reference.

I create two tables as you mentioned.

Then I create a calculated column and here is the DAX code.

Column = 
SUMX(
    'Table',
    IF(
        'Date'[Date] >= 'Table'[Start Date] && 'Date'[Date] <= 'Table'[End Date],
        'Table'[Cost],
        0
    )
)

Best Regards

Yilong Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[bhanu_gautam]

@Anonymous , You can try below DAX for this

DAX
TotalCost :=
VAR SelectedDate = SELECTEDVALUE('DateTable'[Date])
RETURN
SUMX(
FILTER(
'CostTable',
SelectedDate >= 'CostTable'[Start Date] && SelectedDate <= 'CostTable'[End Date]
),
'CostTable'[Cost]
)