Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
RY2019
Frequent Visitor

Calculate Previous Act cumulated

Hi Power BI Users,

 

i need your help with a dax calculation to get a previous act cumulated. 

 

  ActActFCFC
CostVersion31.10.202030.11.202031.12.202031.01.2021
AP15.00015.00017.00018.000
BP15.00033.00025.00020.000
CP15.00014.00020.00033.000
TotalP115.00062.00062.00071.000

 

     
  ActActActFC
CostVersion31.10.202030.11.202031.12.202031.01.2021
AP25.00015.00016.00018.000
BP25.00033.00025.00020.000
CP25.00014.00017.000 33.000
TotalP215.00062.00058.00071.000

 

My Result when I have the Filter option (Version P2):

 

CostAct CumulatedFC CumulatedPrevious Act
A36.00018.00020.000
B63.00020.00038.000
C36.00033.00019.000
Total135.00071.00077.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?

8 REPLIES 8
Tanushree_Kapse
Impactful Individual
Impactful Individual

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.

Hi @Tanushree_Kapse 

 

I try it but then I got an empty result:

RY2019_0-1629901510253.png

My existing Measure looks like this:

RY2019_2-1629901697707.png

 

 

@RY2019 ,

Try this:

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

 

@Tanushree_Kapse 

 

Same Result as existing measure:

RY2019_0-1629902425245.png

 

Hi @RY2019 ,

 

Please find the attached screnshot:

Tanushree_Kapse_0-1629974368733.png

 

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

 

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:

 

RY2019_0-1630317410486.png

 

Hi @RY2019 

Can you please share your pbix file

 

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Top Solution Authors