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

Grow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.

Reply
Junaid11
Helper V
Helper V

Subtract the value by fixed value to get next subtracted value

Hello,
I have a budget value which is fixed for a year. I want to subtract for each month and remaining value should subtract the next month.
The table us given below:

D

F

 

25

475

500

50

425

100

325

75

250

45

205

250

-45

My constant value is 500 so if we subtract 500-25 = 475.
475 should subtract next valuein the column like 475-50 = 425, 425-100=325, 325-75 = 50.Please help get the column F. Your help would be highly appreciated.
Thank you

 

1 ACCEPTED SOLUTION
ChrisMendoza
Resident Rockstar
Resident Rockstar

@Junaid11 - Here's one one to do it where you create a running total.

image.png






Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

Proud to be a Super User!



View solution in original post

3 REPLIES 3
Shaurya
Memorable Member
Memorable Member

Hi @Junaid11,

 

Use your date column to rank the values in column D.

 

Rank = RANKX('Table','Table'[Date],,ASC)

 

Then use this formula to create column F.

 

F = 500 - SUMX('Table',IF('Table'[Rank]<=EARLIER('Table'[Rank]),'Table'[D],0))

 

Works for you? Mark this post as a solution if it does!
Check out this blog of mine: How to Export Telemetry Data from Azure IoT Central into Power BI

ChrisMendoza
Resident Rockstar
Resident Rockstar

@Junaid11 - Here's one one to do it where you create a running total.

image.png






Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

Proud to be a Super User!



@Junaid11 - whoops! I just realized I didn't fix the Total row.
image.png






Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

Proud to be a Super User!



Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

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