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 moreShape the future of the Fabric Community! Your insights matter. That’s why we created a quick survey to learn about your experience finding answers to technical questions. Take survey.
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:
Your insights matter. That’s why we created a quick survey to learn about your experience finding answers to technical questions.
User | Count |
---|---|
21 | |
20 | |
20 | |
13 | |
13 |
User | Count |
---|---|
41 | |
28 | |
25 | |
23 | |
21 |