I am trying to get a sum of one column based on the date in another.
Count | Date |
---|---|
100 | 05/01/2021 |
200 | 05/01/2021 |
300 | 05/01/2021 |
100 | 06/01/2021 |
200 | 06/01/2021 |
400 | 06/01/2021 |
100 | 07/01/2021 |
300 | 07/01/2021 |
500 | 07/01/2021 |
In SQL what I want is:
SELECT SUM([COUNT]) WHERE [Date] = MIN([Date]) FROM [rpt_data]
I can't figure out how to do this in DAX. I tried:
CALCULATE( SUM([Count]), MIN([Date]) )
But it says that I can't use MIN as a filter.
I know this works: CALCULATE( SUM([Count]), rpt_data[Date] = "07/01/2021" )
But I need to know the first and last dates dynamically.
I need another with MAX[Date]) but I figure if I get one figured out I'll figure out the other.