Forum Discussion

shashiPaul1570's avatar
shashiPaul1570
Frequent Visitor
3 years ago
Solved

DAX related

Hi Community,
I am trying to calculate the previously paid indeminity for my claims no. where claim type is 2 and transaction date is less than the selected date. 

tryPreviously Paid Indeminity =



Measure =
VAR X=FIRSTDATE('Date table'[Date])*1
VAR Y=CALCULATE(SUM('CP-CPBinder'[Amount Excl_ VAT2]),'CP-CPBinder'[cp-date(int)]<X)

RETURN Y
I have tried it with 
Amount Excl_ VAT2 =
CALCULATE(
SUM(CP-CPBinder'[Amount Excl_ VAT2]),
CP-CPBinder'[CP Type] = 2,
CP-CPBinder'[Transaction Date] < SELECTEDVALUE('Date Table'[Date])
)
it is giving me blank only.  What I need is the amount that have been paid prior to the selected date for any claim no. 

Regards
Shashi Paul 
Show and Tell

5 Replies

    • shashiPaul1570's avatar
      shashiPaul1570
      Frequent Visitor
      3 years ago

      Please find the sample data. 

      No_Transaction DateTypeAmount
      10061010/11/202120
      10131110/11/202110
      10131210/11/2021278.29
      10131310/11/20212260.98
      10131410/11/202110
      10131510/11/202120
      10131610/11/202110
      10131710/11/202110
      10131810/11/202110
      10131910/11/202120
      10132013/12/2021178.29
      10132113/12/20211260.98
      10132213/12/20211182.68
      10132310/11/202110
      10132410/11/202100
      10132510/11/202100
      10132610/11/202100
      10132710/11/202120
      10132810/11/202120
      10132910/11/202120
      10173010/11/202120
      10173110/11/202120
      10193210/11/202120
      10213310/11/202120
      10213410/11/202120
      10213510/11/202100
      10213610/11/202100
      10213710/11/202100
      10323810/11/202120
      10323910/11/202110
      10324010/11/202100
      10324110/11/202100
      10324210/11/202100
      10324310/11/202120
      10344410/11/202110
      10344510/11/202120
      10374610/11/202110
      10374710/11/202120
      10384810/11/202110
      10384910/11/202100
      10385010/11/202100
      10385110/11/202100
      10385210/11/202120
      10395310/11/202110
      10395410/11/202100
      10395510/11/202100
      10395610/11/202100
      10405710/11/202110
      10405810/11/202110
      10405910/11/202120
      10406010/11/202110
      10416110/11/202120
      10416210/11/202120
      10426310/11/202120
      10426410/11/202120
      10426510/11/202100
      10426610/11/202110
      10426710/11/202100
      10426810/11/202100
      10426910/11/202100
      10427010/11/202120
      10457110/11/202110
      10457210/11/202100
      10457310/11/202100
      10457410/11/202100
      10457510/11/202120
      10467610/11/202120
      10477710/11/202120
      10497810/11/202120
      10497910/11/202100
      10498010/11/202100
      10498110/11/202100
      10508210/11/202110
      10508310/11/202100
      10508410/11/202100
      10508510/11/202100
      10508610/11/202120
      10548710/11/202110
      10548810/11/202120
      10568910/11/202120
      10569010/11/202100
      10569110/11/202100
      10569210/11/202100
      10569310/11/202120
      10569410/11/202100
      10569510/11/202100
      10569610/11/202100
      10579710/11/202120
      10579810/11/202100
      • NaveenGandhi's avatar
        NaveenGandhi
        Icon for Memorable Member rankMemorable Member
        3 years ago

        Hello shashiPaul1570 

        Try the below DAX.

        Previous =
        VAR CurrentDate = max('Previous Indemnity'[Transaction Date])
        RETURN
            CALCULATE (
                SUM ( 'previous indemnity'[Amount] ),
                FILTER (
                    ALL ( 'previous indemnity' ),
                    'previous indemnity'[Transaction Date] = CALCULATE ( MAX ( 'previous indemnity'[Transaction Date] ), 'previous indemnity'[Transaction Date] < CurrentDate )
                )
            )

        Let me know if you have any questions or issues.

         

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