Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
1 year ago
Solved

Create a conditional sum using a date table

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 ...
Need Help
  • Anonymous's avatar
    Anonymous
    1 year ago

    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's avatar
bhanu_gautam
Super User
1 year ago

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]
)