March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early bird discount ends December 31.
Register NowBe one of the first to start using Fabric Databases. View on-demand sessions with database experts and the Microsoft product team to learn just how easy it is to get started. Watch 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:
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!
User | Count |
---|---|
27 | |
17 | |
16 | |
12 | |
11 |
User | Count |
---|---|
38 | |
29 | |
22 | |
20 | |
18 |