topic Re: Need help in calculating SUM and AVERAGE for below problem Statement in DAX in DAX Commands and Tips

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

kailash2908 — Sun, 22 Sep 2024 12:29:03 GMT

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)

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

dharmendars007 — Sun, 22 Sep 2024 13:51:39 GMT

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

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

Jihwan_Kim — Sun, 22 Sep 2024 14:04:33 GMT

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.

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

kailash2908 — Sun, 22 Sep 2024 15:55:43 GMT

Total is 2720 , it should be 720

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

kailash2908 — Sun, 22 Sep 2024 16:00:03 GMT

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

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

Jihwan_Kim — Mon, 23 Sep 2024 03:07:00 GMT

Hi,

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

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

dharmendars007 — Mon, 23 Sep 2024 07:56:46 GMT

Hello kailash2908 ,

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

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

kailash2908 — Mon, 23 Sep 2024 12:59:14 GMT

This worked , Thank you so much