DAX related

[shashiPaul1570]

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 

[NaveenGandhi]

Hello @shashiPaul1570 

Can you provide sample data and desired output to understand the problem better?

Thanks,
Naveen

[shashiPaul1570]

Please find the sample data. 

No_	Transaction Date	Type	Amount
100610	10/11/2021	2	0
101311	10/11/2021	1	0
101312	10/11/2021	2	78.29
101313	10/11/2021	2	260.98
101314	10/11/2021	1	0
101315	10/11/2021	2	0
101316	10/11/2021	1	0
101317	10/11/2021	1	0
101318	10/11/2021	1	0
101319	10/11/2021	2	0
101320	13/12/2021	1	78.29
101321	13/12/2021	1	260.98
101322	13/12/2021	1	182.68
101323	10/11/2021	1	0
101324	10/11/2021	0	0
101325	10/11/2021	0	0
101326	10/11/2021	0	0
101327	10/11/2021	2	0
101328	10/11/2021	2	0
101329	10/11/2021	2	0
101730	10/11/2021	2	0
101731	10/11/2021	2	0
101932	10/11/2021	2	0
102133	10/11/2021	2	0
102134	10/11/2021	2	0
102135	10/11/2021	0	0
102136	10/11/2021	0	0
102137	10/11/2021	0	0
103238	10/11/2021	2	0
103239	10/11/2021	1	0
103240	10/11/2021	0	0
103241	10/11/2021	0	0
103242	10/11/2021	0	0
103243	10/11/2021	2	0
103444	10/11/2021	1	0
103445	10/11/2021	2	0
103746	10/11/2021	1	0
103747	10/11/2021	2	0
103848	10/11/2021	1	0
103849	10/11/2021	0	0
103850	10/11/2021	0	0
103851	10/11/2021	0	0
103852	10/11/2021	2	0
103953	10/11/2021	1	0
103954	10/11/2021	0	0
103955	10/11/2021	0	0
103956	10/11/2021	0	0
104057	10/11/2021	1	0
104058	10/11/2021	1	0
104059	10/11/2021	2	0
104060	10/11/2021	1	0
104161	10/11/2021	2	0
104162	10/11/2021	2	0
104263	10/11/2021	2	0
104264	10/11/2021	2	0
104265	10/11/2021	0	0
104266	10/11/2021	1	0
104267	10/11/2021	0	0
104268	10/11/2021	0	0
104269	10/11/2021	0	0
104270	10/11/2021	2	0
104571	10/11/2021	1	0
104572	10/11/2021	0	0
104573	10/11/2021	0	0
104574	10/11/2021	0	0
104575	10/11/2021	2	0
104676	10/11/2021	2	0
104777	10/11/2021	2	0
104978	10/11/2021	2	0
104979	10/11/2021	0	0
104980	10/11/2021	0	0
104981	10/11/2021	0	0
105082	10/11/2021	1	0
105083	10/11/2021	0	0
105084	10/11/2021	0	0
105085	10/11/2021	0	0
105086	10/11/2021	2	0
105487	10/11/2021	1	0
105488	10/11/2021	2	0
105689	10/11/2021	2	0
105690	10/11/2021	0	0
105691	10/11/2021	0	0
105692	10/11/2021	0	0
105693	10/11/2021	2	0
105694	10/11/2021	0	0
105695	10/11/2021	0	0
105696	10/11/2021	0	0
105797	10/11/2021	2	0
105798	10/11/2021	0	0

[NaveenGandhi]

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.

[shashiPaul1570]

Hi Naveen, 
It is not giving me the right result when I am using this measure in a table. 

[NaveenGandhi · Accepted solution]

@shashiPaul1570 

Please check the attached PBIX and let me know if you need any changes.