topic Re: Calculate Previous Act cumulated in DAX Commands and Tips

Calculate Previous Act cumulated

RY2019 — Wed, 25 Aug 2021 13:27:14 GMT

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?

Re: Calculate Previous Act cumulated

Tanushree_Kapse — Wed, 25 Aug 2021 13:59:06 GMT

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.

Re: Calculate Previous Act cumulated

RY2019 — Wed, 25 Aug 2021 14:28:35 GMT

Hi Tanushree_Kapse

I try it but then I got an empty result:

My existing Measure looks like this:

Re: Calculate Previous Act cumulated

Tanushree_Kapse — Wed, 25 Aug 2021 14:37:08 GMT

RY2019 ,

Try this:

Previous Act = Calculate (Sum('Cost'[Act Cost]), Parallelperiod(tbldate[date],-1 , Month))

Re: Calculate Previous Act cumulated

RY2019 — Wed, 25 Aug 2021 14:40:42 GMT

Tanushree_Kapse

Same Result as existing measure:

Re: Calculate Previous Act cumulated

Tanushree_Kapse — Thu, 26 Aug 2021 10:44:02 GMT

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

Re: Calculate Previous Act cumulated

RY2019 — Mon, 30 Aug 2021 09:58:32 GMT

Hi Tanushree_Kapse

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:

Re: Calculate Previous Act cumulated

Tanushree_Kapse — Mon, 30 Aug 2021 10:01:00 GMT

Hi RY2019

Can you please share your pbix file

Re: Calculate Previous Act cumulated

RY2019 — Wed, 01 Sep 2021 12:45:28 GMT

Hi Tanushree_Kapse

here is the link:

https://www.dropbox.com/s/exmwmczmcx02g5r/Test.pbix?dl=0