cancel
Showing results for
Did you mean:

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Post Patron

## Sum of revenue in the last 3 months

Hi,

I have this data as below with date and revenue and I want to calculate sum of revenue in the last 3 months.

The desired output is the column in red below.  Please can someone advise on how to achieve this. Thank you.

 ActualDate Revenue Sum of revenue in the last 3 months 01/01/2021 100 01/02/2021 50 01/03/2021 200 350 01/04/2021 75 325 01/05/2021 100 375 01/06/2021 50 225 01/07/2021 20 170 01/08/2021 30 100 01/09/2021 50 100 01/10/2021 40 120 01/11/2021 100 190 01/12/2021 20 160

1 ACCEPTED SOLUTION
Super User

1) Started with a table called "DemoTable":

2) Created a calculated table called Calendar with this DAX:

``````Calendar =
CALENDAR ( DATE ( 2021, 01, 01 ), DATE ( 2021, 12, 31 ) ),
"Month", DATE ( YEAR ( [Date] ), MONTH ( [Date] ), 1 ) // Format as MMMM YYYY
)``````

Set both columns it creates to type "Date" and format the month column as

3) Mark "Calendar" as Date table.

4) In the "DemoTable" create a measure "Sum of Revenue":

``Sum of Revenue = SUM ( DemoTable[Revenue] )``

5) In the "DemoTable" create a measure "Sum of Prior 3 Months":

``````Sum of Prior 3 Months =
VAR LatestVisibleDate = LASTDATE ( 'Calendar'[Date] )

VAR EarliestRevenueDate =
CALCULATE(
FIRSTDATE ( DemoTable[ActualDate] ),
REMOVEFILTERS ( 'Calendar' )
)

VAR Date3MonthsEarlier = DATEADD(LatestVisibleDate, -3, MONTH )

VAR Result =
CALCULATE (
[Sum of Revenue],
DATESINPERIOD( 'Calendar'[Date], LatestVisibleDate, -3, MONTH )
)

RETURN
IF ( Date3MonthsEarlier >= EarliestRevenueDate , Result )``````

6) Drop into a matrix visual like this:

Ben Dobbs

Did I answer your question? Mark my post as a solution! This will help others on the forum!
6 REPLIES 6
Super User

Hi,

Hope this helps.

Regards,
Ashish Mathur
http://www.ashishmathur.com
Super User

@bml123  if you have a Calendar tbl like this

``````let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("XdgxbuxQDEPRvaT+gEU+yxqvJcj+txGk+eBVycpnHjGF+P39pUuXy/r6+fcXnOFkuDN0hifDZPhkeDOokGAQEIJCYAgOASJIBIpgMSzme8BiWAyLYTEshsWwGJYDywmLLwRnOBnuDJ3hyTAZPhlefLSQCAJCUAgMwSFABIlAESyGxXwPWAyLYTEshsWwZFEnn//k8598/pPPf/L5Tz7/yec/F77z4qOFBIOAEBQCQ3AIEEEi/mRYDIv5HrAYFsNiWAyLYeHzG5YDS/5P7gvBGU6GO0NneDJMhk+GFx8tJIKAEBQCQ3AIEEEiUASLYTHfAxbDYlgMi2ExLIbFsGRRndV0VtNZTWc1ndV0VtNZTWc1ndU0qmlU06imUU2jmkY1jWoa1TSqaVTTqKZRTaOaRjWNahrVNKppVNOoplFNr2ryP/RcCM5wMtwZOsOTYTJ8Mrz4aCERBISgEBiCQ4AIEoEiWAyL+R6wGBbDYlgMi2ExLIYli5qsZrKayWomq5msZrKayWomq5msZlDNoJpBNYNqBtUMqhlUM6hmUM2gmkE1g2oG1QyqGVQzqGZQzaCaQTWDamZVk/+hz4XgDCfDnaEzPBkmwyfDi48WEkFACAqBITgEiCARKILFsJjvAYthMSyGxbAYFsNiWA4sWdR7ITjDyXBn6AxPhsnwyfDio4VEEBCCQmAIDgEiSASKYDEs5nvAYlgMi2ExLIbFsBgWXECF67Rwnhbu08KBWrhQCydq4UYtHKmFK7V4phbv1OKhWrxUi6dq8VYtHqvFa7V4rhbv1UJdf3G9EVWmylSZKlNlqkyVqVrF5V9MXBk4M3Bn4NDApYFTA7cGjg1cG9bcsPaGNTisxWFNDmtzWKPDWh3W7LB2hzU8rOVhTQ9re1jjw1of1vyw9oc1QKwFYk0QWB2E2UHYHYThQVgehOlB2B6E8UFYH8T5QdwfxAFCXCDECULcIMQRQlwhxBlC3CHEIUJcIsQpQtwixDFCXCPEOULcI8RB4i9StYr7/wf8+QU=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}}),
#"Inserted Year" = Table.AddColumn(#"Changed Type", "Year", each Date.Year([Date]), Int64.Type),
#"Inserted Month" = Table.AddColumn(#"Inserted Year", "Month", each Date.Month([Date]), Int64.Type),
#"Sorted Rows" = Table.Sort(#"Inserted Month",{{"Date", Order.Ascending}}),
#"Grouped Rows" = Table.Group(#"Sorted Rows", {"Year", "Month"}, {{"ad", each _, type table [Date=nullable date, Year=number, Month=number]}}),
in

and a schema like this

You can achieve it with this measure

``````sumLast3Months =
VAR _upper =
CALCULATE ( MAX ( 'Calendar'[Index] ), tbl )
VAR _lower =
MINX (
TOPN (
3,
SUMMARIZE (
FILTER ( ALL ( 'Calendar' ), 'Calendar'[Index] <= _upper ),
'Calendar'[Index]
),
'Calendar'[Index], DESC
),
'Calendar'[Index]
)
VAR _sum =
CALCULATE (
SUM ( tbl[Revenue] ),
FILTER (
ALL ( 'Calendar' ),
'Calendar'[Index] >= _lower
&& 'Calendar'[Index] <= _upper
)
)
RETURN
_sum
``````

Proud to be a Super User!
My custom visualization projects
Plotting Live Sound: Viz1
Beautiful News:Viz1, Viz2, Viz3
Visual Capitalist: Working Hrs
Super User

1) Started with a table called "DemoTable":

2) Created a calculated table called Calendar with this DAX:

``````Calendar =
CALENDAR ( DATE ( 2021, 01, 01 ), DATE ( 2021, 12, 31 ) ),
"Month", DATE ( YEAR ( [Date] ), MONTH ( [Date] ), 1 ) // Format as MMMM YYYY
)``````

Set both columns it creates to type "Date" and format the month column as

3) Mark "Calendar" as Date table.

4) In the "DemoTable" create a measure "Sum of Revenue":

``Sum of Revenue = SUM ( DemoTable[Revenue] )``

5) In the "DemoTable" create a measure "Sum of Prior 3 Months":

``````Sum of Prior 3 Months =
VAR LatestVisibleDate = LASTDATE ( 'Calendar'[Date] )

VAR EarliestRevenueDate =
CALCULATE(
FIRSTDATE ( DemoTable[ActualDate] ),
REMOVEFILTERS ( 'Calendar' )
)

VAR Date3MonthsEarlier = DATEADD(LatestVisibleDate, -3, MONTH )

VAR Result =
CALCULATE (
[Sum of Revenue],
DATESINPERIOD( 'Calendar'[Date], LatestVisibleDate, -3, MONTH )
)

RETURN
IF ( Date3MonthsEarlier >= EarliestRevenueDate , Result )``````

6) Drop into a matrix visual like this:

Ben Dobbs

Did I answer your question? Mark my post as a solution! This will help others on the forum!
Post Patron

@bcdobbsI have tried your solution and it worked perfectly fine. Thank you so much.

Helper I

Hi,

Try writing the following measure:

``````Sum of Revenue =
VAR __CurrentDate = SELECTEDVALUE( Table[ActualDate] )

VAR __Result =
CALCULATE(
SUM( Table[Revenue]),
FILTER(
ALL( Table[ActualDate] ),
DATEDIFF( Table[ActualDate], __CurrentDate, MONTH ) <= 2
)
)

RETURN
__Result``````

where 'Table' is the table containing the ActualDate and Revenue columns.

Super User

Have a look at this demo file: Demo File

Will send a walk through shortly.

Ben Dobbs

Did I answer your question? Mark my post as a solution! This will help others on the forum!

Announcements

#### New forum boards available in Real-Time Intelligence.

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

#### Power BI Monthly Update - May 2024

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

#### Fabric certifications survey

Certification feedback opportunity for the community.

Top Solution Authors
Top Kudoed Authors