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 someone able to help with the query?

Thanks

 
CostStart DateEnd date
1001/01/202405/01/2024
2008/01/202409/01/2024
2010/01/202415/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
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.

2 Replies

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

  • Anonymous's avatar
    Anonymous
    Not applicable
    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.