Need help in calculating SUM and AVERAGE for below problem Statement in DAX

[kailash2908]

I Have below query to solve in Power bi using DAX :

I have a table with transactions amount ,transaction dates with payer and vendor id, I have closed dates for payer , payee combination ..
I need to find the sum oflast 12 months transaction amount , if difference between closed dated and earliest transaction date goes back more than 24 months,
I need to find last 6 months average of transaction amount , if difference between closed dated and earliest transaction date goes back between 12 and 23 months

I need this calculation in a single column

Example Table : 

Table 1:

 

Payer	Vendor	Transaction DATE	Closed Date	Transaction Amount
111	123	9/20/2024	9/22/2024	100
111	123	10/22/2023	9/22/2024	20
111	123	5/22/2023	9/22/2024	100
111	123	6/22/2021	9/22/2024	200
111	123	5/22/2021	9/22/2024	300
111	555	8/20/2024	8/31/2024	500
111	555	7/20/2024	8/31/2024	600
111	555	6/20/2024	8/31/2024	700
111	555	2/20/2024	8/31/2024	800

Resulting Matrix I need : 

Payer	Vendor	Earliest Transaction DATE	Closed Date	Diff Between Dates	Calculated Amount
111	123	5/22/2021	9/22/2024	Greater than 24	120	(This is Sum of Last 12 months)
111	555	5/20/2024	8/31/2024	Greatre than 12 less than 23	550	(This average of last 6 months)

[dharmendars007]

Hello @kailash2908 , 

I see there the earliest dates you have mentioned for vendor "555" is not present in the table you have provided "5/20/2024' therefore Average is coming blank, kindly check this..
Apart from that based on other criteria i was able to create measure please check this and use this pattern to further modify your measure.

Measure 1 

Earlydate = CALCULATE(MIN('Table'[Transaction DATE]),

                                    ALLEXCEPT('Table', 'Table'[Payer], 'Table'[Vendor]))

Measure 2 

Diff Between Dates = DATEDIFF([Earlydate], MAX('Table'[Closed Date]), MONTH)

Measure 3 (Combine 1 and 2 )

Values =

VAR EarliestDate = [Earlydate]

VAR ClosedDate = Max('Table'[Closed Date])

VAR DiffMonths = [Diff Between Dates]

VAR Last12MonthsAmount =

    CALCULATE(

        Sum('Table'[Transaction Amount]),

        FILTER(

            'Table','Table'[Transaction DATE] >= EDATE(ClosedDate, -12)))

VAR Last6MonthsAverage =

    CALCULATE(

    AVERAGE('Table'[Transaction Amount]),

    FILTER(

        'Table',

        'Table'[Transaction DATE] <= MAX('Table'[Closed Date]) &&

        'Table'[Transaction DATE] >= EDATE(MAX('Table'[Closed Date]), -6)))

RETURN

        IF(DiffMonths > 24, Last12MonthsAmount,

            IF(

            DiffMonths >= 12 && DiffMonths <= 23, Last6MonthsAverage, BLANK()))

If you find this helpful , please mark it as solution which will be helpful for others and Your Kudos/Likes are much appreciated!

Thank You

Dharmendar S

LinkedIN 

[Jihwan_Kim]

Hi,

I am not sure if I understood your question correctly, but I tried to create a sample pbix file like below, and I hope you can start from the below to make the result as you expected. Please check the below picture and the attached pbix file.

[kailash2908]

Total is 2720 , it should be 720

[Jihwan_Kim · Accepted solution]

Hi,

If you want 720 on Total row, please check the below picture and the attached pbix file.

[kailash2908]

This worked , Thank you so much

[dharmendars007]

Hello @kailash2908 , 

I see there the earliest dates you have mentioned for vendor "555" is not present in the table you have provided "5/20/2024' therefore Average is coming blank, kindly check this..
Apart from that based on other criteria i was able to create measure please check this and use this pattern to further modify your measure.

Measure 1 

Earlydate = CALCULATE(MIN('Table'[Transaction DATE]),

                                    ALLEXCEPT('Table', 'Table'[Payer], 'Table'[Vendor]))

Measure 2 

Diff Between Dates = DATEDIFF([Earlydate], MAX('Table'[Closed Date]), MONTH)

Measure 3 (Combine 1 and 2 )

Values =

VAR EarliestDate = [Earlydate]

VAR ClosedDate = Max('Table'[Closed Date])

VAR DiffMonths = [Diff Between Dates]

VAR Last12MonthsAmount =

    CALCULATE(

        Sum('Table'[Transaction Amount]),

        FILTER(

            'Table','Table'[Transaction DATE] >= EDATE(ClosedDate, -12)))

VAR Last6MonthsAverage =

    CALCULATE(

    AVERAGE('Table'[Transaction Amount]),

    FILTER(

        'Table',

        'Table'[Transaction DATE] <= MAX('Table'[Closed Date]) &&

        'Table'[Transaction DATE] >= EDATE(MAX('Table'[Closed Date]), -6)))

RETURN

        IF(DiffMonths > 24, Last12MonthsAmount,

            IF(

            DiffMonths >= 12 && DiffMonths <= 23, Last6MonthsAverage, BLANK()))

If you find this helpful , please mark it as solution which will be helpful for others and Your Kudos/Likes are much appreciated!

Thank You

Dharmendar S

LinkedIN 

[kailash2908]

Total is wrong right it is showing 2720, ideally it should be 120 in the total