Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now

Reply
kailash2908
Frequent Visitor

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

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 VendorEarliest Transaction DATEClosed DateDiff Between DatesCalculated Amount 
1111235/22/20219/22/2024Greater than 24120(This is Sum of Last 12 months)
1115555/20/20248/31/2024Greatre than 12 less than 23 550(This average of last 6 months)
1 ACCEPTED SOLUTION

Hi,

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

 

Jihwan_Kim_0-1727060795418.png

 

 

If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.


Visit my LinkedIn page by clicking here.


Schedule a meeting with me to discuss further by clicking here.

View solution in original post

7 REPLIES 7
dharmendars007
Solution Sage
Solution Sage

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.

 

dharmendars007_1-1727012989137.png

 

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
Super User
Super User

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.

Jihwan_Kim_1-1727013863076.png

 

 

If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.


Visit my LinkedIn page by clicking here.


Schedule a meeting with me to discuss further by clicking here.

kailash2908_0-1727020504345.png

Total is 2720 , it should be 720

Hi,

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

 

Jihwan_Kim_0-1727060795418.png

 

 

If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.


Visit my LinkedIn page by clicking here.


Schedule a meeting with me to discuss further by clicking here.

This worked , Thank you so much

dharmendars007
Solution Sage
Solution Sage

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.

 

dharmendars007_1-1727012989137.png

 

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 

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

Helpful resources

Announcements
November Carousel

Fabric Community Update - November 2024

Find out what's new and trending in the Fabric Community.

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.

Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

Check out the November 2024 Power BI update to learn about new features.

Top Solution Authors