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
bw70316
Helper V
Helper V

Difference between monthly sales, not cumulative total

I would simply like the difference or the month's total sales by month, and not a cummulative figure. For example, I am currently getting the following output:

Revenues ReceivedMONTH
$9,061,289.6611
$8,924,656.0710
$7,099,541.629
$5,301,671.968
$3,804,197.987


What I would Like is:


Revenues ReceivedMONTH
$136,63311
$1,825,114.4510
$1,797,8709
$1,497,4748
$3,804,1977


The ACTUAL Sales AMOUNT by Month and not a cummlative total. 

1 ACCEPTED SOLUTION

I had trouble getting this to work. I think its because the months I had were formed through a number of revenue/expense items. I did indeed get the correct totals in the column, but b/c the consists of numerous expense items, the numbers cumulate.

trial.png
I was able to get what I desired with this equation:

TotalRev2 =
VAR CumRev =
CALCULATE([Sum Rev Rec.], FILTER(ALL(Revenues), Revenues[MonthNUM]=MAX(Revenues[MonthNUM])-1))
VAR CurRev= [Sum Rev Rec.]

RETURN


(CurRev-CumRev)


Thanks for your reply and assistance.

View solution in original post

2 REPLIES 2
v-juanli-msft
Community Support
Community Support

Hi @bw70316

Create columns

Column = CALCULATE(SUM(Sheet4[Revenues Received]),FILTER(ALL(Sheet4),[MONTH]=EARLIER(Sheet4[MONTH])-1))

Column 2 = [Revenues Received]-[Column]

15.png

 

 

You could change the decimal palces.

In your example, $1,825,114.45 should set 2, others should set 0, but one column only support set one kind value, so i set 2 for all.

 

Best Regards

Maggie

 

I had trouble getting this to work. I think its because the months I had were formed through a number of revenue/expense items. I did indeed get the correct totals in the column, but b/c the consists of numerous expense items, the numbers cumulate.

trial.png
I was able to get what I desired with this equation:

TotalRev2 =
VAR CumRev =
CALCULATE([Sum Rev Rec.], FILTER(ALL(Revenues), Revenues[MonthNUM]=MAX(Revenues[MonthNUM])-1))
VAR CurRev= [Sum Rev Rec.]

RETURN


(CurRev-CumRev)


Thanks for your reply and assistance.

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.