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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Dorinb
Frequent Visitor

Cumulative multiply previous rows in power query or calculated column

Hi,

 

For a set of percentages split by location and period , I need to obtain a detail for each location and for each period as a percentage of the previous months multiplied, as follows:

 

Capture 5.JPG

 

Cheers,

Dorin

2 REPLIES 2
amitchandak
Super User
Super User

In case you only looking this month and last month. Have you calculation like this and use them in multiplication

 

MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD('Date'[Date Filer]))
 Last month Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(ENDOFMONTH(dateadd('Date'[Date Filer],-1,MONTH))))

Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution. In case it does not help, please provide additional information and mark me with @
Thanks.

My Recent Blog - https://community.powerbi.com/t5/Community-Blog/Comparing-Data-Across-Date-Ranges/ba-p/823601

Thanks @amitchandak ,

 

However it's not what I need.

First of all If you take a look at the expected result:

1.for February the solution should create lines for all the periods from the beginning to the current period - in this case two lines both January and February and those 2 months to have calculated amounts as follows:

  • Jan, first month, to have a multiplication of the value from January and the one shown in February - only February
  • Feb to have Feb values

2. For March we would need to create 3 lines from the beginning of the period,- in this case Jan, Feb and Mar and have the values computed as follows:

  • Jan should have the valle from Jan multiplied with Feb and Mar
  • Feb should have the values from Feb multiplied with Mar
  • Mar should have Mar values.

It's quite tricky. Hope my explanations clarifies a bit the problem.

 

Cheers,

Dorin

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.