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

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

Reply
bml123
Post Patron
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.

 

ActualDateRevenueSum of revenue in the last 3 months
01/01/2021100 
01/02/202150 
01/03/2021200350
01/04/202175325
01/05/2021100375
01/06/202150225
01/07/202120170
01/08/202130100
01/09/202150100
01/10/202140120
01/11/2021100190
01/12/202120160

 

1 ACCEPTED SOLUTION
bcdobbs
Super User
Super User

1) Started with a table called "DemoTable":

bcdobbs_0-1640726059437.png

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

Calendar = 
    ADDCOLUMNS (
        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 

bcdobbs_1-1640726203929.png

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:

bcdobbs_2-1640726389208.png

 

 



Ben Dobbs

LinkedIn | Twitter | Blog

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

View solution in original post

6 REPLIES 6
Ashish_Mathur
Super User
Super User

Hi,

You may download my PBI file from here.

Hope this helps.

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
smpa01
Super User
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]}}),
    #"Added Index" = Table.AddIndexColumn(#"Grouped Rows", "Index", 1, 1, Int64.Type),
    #"Expanded ad" = Table.ExpandTableColumn(#"Added Index", "ad", {"Date"}, {"Date"})
in
    #"Expanded ad"

 

and a schema like this

smpa01_0-1640742336211.png

 

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

 

 

smpa01_1-1640742429328.png

 

 

 

 

 

Did I answer your question? Mark my post as a solution!
Proud to be a Super User!
My custom visualization projects
Plotting Live Sound: Viz1
Beautiful News:Viz1, Viz2, Viz3
Visual Capitalist: Working Hrs
bcdobbs
Super User
Super User

1) Started with a table called "DemoTable":

bcdobbs_0-1640726059437.png

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

Calendar = 
    ADDCOLUMNS (
        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 

bcdobbs_1-1640726203929.png

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:

bcdobbs_2-1640726389208.png

 

 



Ben Dobbs

LinkedIn | Twitter | Blog

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

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

Thanks everyone for your prompt answers

askhanduja
Helper I
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.

 

If you think the above answers your question please mark it as the answer. A thumbs up would be greatly appreciated too.

bcdobbs
Super User
Super User

Have a look at this demo file: Demo File 

Will send a walk through shortly.



Ben Dobbs

LinkedIn | Twitter | Blog

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

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.

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.