Forum Discussion

hiba_aziz's avatar
hiba_aziz
Frequent Visitor
2 years ago
Solved

Calculate table with Multiple entry for a date

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: 

 

DateIDcostsub
1/1/2023X5d1
1/1/2023X2d2
1/2/2023X9

d1

1/2/2023

X2

d2

1/2/2023

Y20

d1

1/3/2023

X50

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.

 

 

Need Help
  • Anonymous's avatar
    Anonymous
    2 years ago

    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.

2 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    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.