Calculate table with Multiple entry for a date

[hiba_aziz]

Hi All!

I want to calculate the date where the cost is higher than a previous cost: 

For example:

if I looking at ID = X , previous cost = 10$, current date = 12/31/2022

Input: 

Date	ID	cost	sub
1/1/2023	X	5	d1
1/1/2023	X	2	d2
1/2/2023	X	9	d1
1/2/2023	X	2	d2
1/2/2023	Y	20	d1
1/3/2023	X	50	d1

The dax should return the date where the cost is higher than previous cost 10$. Correct return is 1/2/2023, where for ID X we sum the cost for all subs (2+9).

Any help with the dax would be much appreciated.

[Anonymous · Accepted solution]

Hi @hiba_aziz 

Please try this:

First of all, create a new same table:

Then create a measure to calculate the sum of the cost of the table (2) separately:

SumCost = 
VAR _currentDate1 = MAX('Table (2)'[Date])
VAR _currentID1 = SELECTEDVALUE('Table (2)'[ID])
RETURN CALCULATE(
        SUM('Table (2)'[cost]),
        FILTER(
            ALLSELECTED('Table (2)'),
            'Table (2)'[ID]=_currentID1
            &&
            'Table (2)'[Date]=_currentDate1
            )
        )

Then create a measure to return the target date:

TestDate = 
VAR _currentDate = MAX('Table'[Date])
VAR _currentID = SELECTEDVALUE('Table'[ID])
VAR _CurrentSum = CALCULATE(
                    SUM('Table'[cost]),
                    FILTER(
                        ALLSELECTED('Table'),
                        'Table'[Date]=_currentDate
                        &&
                        'Table'[ID]=_currentID
                        )
                    )
RETURN CALCULATE(
        MIN('Table (2)'[Date]),
        FILTER(
            ALLSELECTED('Table (2)'),
            [SumCost]>_CurrentSum
            &&
            'Table (2)'[Date]>_currentDate
            &&
            'Table (2)'[ID]=_currentID
            )
        )

The result is as follow:

Best Regards,

Zhengdong Xu

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

[hiba_aziz]

@Ritaf1983 @Ahmedx  @amitchandak @Fowmy