Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreGet 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
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) |
Solved! Go to Solution.
Hi,
If you want 720 on Total row, please check the below picture and the attached pbix file.
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.
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
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
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.
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.
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.
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.
This worked , Thank you so much
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
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
Total is wrong right it is showing 2720, ideally it should be 120 in the total
Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
User | Count |
---|---|
24 | |
21 | |
19 | |
14 | |
11 |
User | Count |
---|---|
43 | |
35 | |
25 | |
24 | |
22 |