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
Hi Power BI Users,
i need your help with a dax calculation to get a previous act cumulated.
Act | Act | FC | FC | ||
Cost | Version | 31.10.2020 | 30.11.2020 | 31.12.2020 | 31.01.2021 |
A | P1 | 5.000 | 15.000 | 17.000 | 18.000 |
B | P1 | 5.000 | 33.000 | 25.000 | 20.000 |
C | P1 | 5.000 | 14.000 | 20.000 | 33.000 |
Total | P1 | 15.000 | 62.000 | 62.000 | 71.000 |
| |||||
Act | Act | Act | FC | ||
Cost | Version | 31.10.2020 | 30.11.2020 | 31.12.2020 | 31.01.2021 |
A | P2 | 5.000 | 15.000 | 16.000 | 18.000 |
B | P2 | 5.000 | 33.000 | 25.000 | 20.000 |
C | P2 | 5.000 | 14.000 | 17.000 | 33.000 |
Total | P2 | 15.000 | 62.000 | 58.000 | 71.000 |
My Result when I have the Filter option (Version P2):
Cost | Act Cumulated | FC Cumulated | Previous Act |
A | 36.000 | 18.000 | 20.000 |
B | 63.000 | 20.000 | 38.000 |
C | 36.000 | 33.000 | 19.000 |
Total | 135.000 | 71.000 | 77.000 |
Now my question is how can I get the Previous Act in the table?
My first try was the dax formula:
Previous Act = Calculate (Sum('Cost'[Act Cost]),Dateadd(tbldate[date],-1,Month))) but I always come to the same total Act (36.000+63.000+36.000=135.000) but it should be Previous Act (20.000+38.000+19.000=77.000).
What is in my formula wrong or is there any other solution?
Hi @RY2019 ,
Use this measure:
Previous Act = Calculate (Sum('Cost'[Act Cost]), Previousmonth(tbldate[date]))
Also, can you show what the output you are getting with your existing measure
Mark this as a solution if I anwered your question.
Thanks.
@RY2019 ,
Try this:
Previous Act = Calculate (Sum('Cost'[Act Cost]), Parallelperiod(tbldate[date],-1 , Month))
Hi @RY2019 ,
Please find the attached screnshot:
You can also find the attached file for your reference:
https://www.dropbox.com/s/w8iais1cr8g5xd3/Sample%20file.pbix?dl=0
Mark this as a solution if I answered your question, kudos are always appreciated.
Thanks
I have the same formula but in your Screenshot there is missing the Version Filter. And Previous Act should change with change the Version. In Version P3 should be the Previous Act the Act of Version P2:
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 |
---|---|
23 | |
21 | |
20 | |
13 | |
12 |
User | Count |
---|---|
41 | |
32 | |
23 | |
23 | |
22 |