cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
thians
Regular Visitor

Total of last 3 month without current month

hi all, 
I need help to calculted the total sales in the last 3 months by selecting slicer week. with the rules. If I choose week 6, the total sales displayed are the previous 3 months, namely November 2021 to January 2022. 

thians_3-1644136124829.png

How do I get the total value of last 3 months without months from the week slicer I chose. thank you

 

2 ACCEPTED SOLUTIONS
MFelix
Super User
Super User

Hi @thians ,

 

When you refer that you want the last 3 months you can try something similar to this:

Totals Last 3 Months =
VAR FirstDateMonth =
    DATE ( YEAR ( MIN ( 'Calendar'[Date] ) ), MONTH ( MIN ( 'Calendar'[Date] ) ), 1 ) - 1
RETURN
    CALCULATE (
        [Measure],
        DATESINPERIOD ( 'Calendar'[Date], FirstDateMonth, -3, MONTH ),
        ALL ( 'Calendar'[Date] )
    )

Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



View solution in original post

v-cgao-msft
Community Support
Community Support

Hi @thians ,

 

According to your description, I created two tables.

vcgaomsft_1-1644479196321.png

Relationship

vcgaomsft_2-1644479373725.png

Try these measure.

StartDate = 
VAR DateFrom = MIN(CALENDAR[Date])
VAR numMonthsBack = 3
VAR StartDate = EOMONTH(DateFrom,(numMonthsBack+1)*-1)+1
RETURN StartDate
EndDate = 
VAR DateFrom = MIN(CALENDAR[Date])
VAR numMonthsBack = 1
VAR EndDate = EOMONTH(DateFrom,numMonthsBack*-1)
RETURN EndDate
Total in last 3 Month = 
var _startDate = [StartDate]
var _endDate = [EndDate]
return
CALCULATE (
    SUM('Table'[Sales]),
    FILTER ( ALL('Table'), 'Table'[Date] >= _startDate && 'Table'[Date] <= _endDate )
)

The result should be like this.

vcgaomsft_3-1644479578083.png

Attaching the pbix file for reference.

 

Best Regards,

Community Support Team _ Gao

 

If there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

View solution in original post

2 REPLIES 2
v-cgao-msft
Community Support
Community Support

Hi @thians ,

 

According to your description, I created two tables.

vcgaomsft_1-1644479196321.png

Relationship

vcgaomsft_2-1644479373725.png

Try these measure.

StartDate = 
VAR DateFrom = MIN(CALENDAR[Date])
VAR numMonthsBack = 3
VAR StartDate = EOMONTH(DateFrom,(numMonthsBack+1)*-1)+1
RETURN StartDate
EndDate = 
VAR DateFrom = MIN(CALENDAR[Date])
VAR numMonthsBack = 1
VAR EndDate = EOMONTH(DateFrom,numMonthsBack*-1)
RETURN EndDate
Total in last 3 Month = 
var _startDate = [StartDate]
var _endDate = [EndDate]
return
CALCULATE (
    SUM('Table'[Sales]),
    FILTER ( ALL('Table'), 'Table'[Date] >= _startDate && 'Table'[Date] <= _endDate )
)

The result should be like this.

vcgaomsft_3-1644479578083.png

Attaching the pbix file for reference.

 

Best Regards,

Community Support Team _ Gao

 

If there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

MFelix
Super User
Super User

Hi @thians ,

 

When you refer that you want the last 3 months you can try something similar to this:

Totals Last 3 Months =
VAR FirstDateMonth =
    DATE ( YEAR ( MIN ( 'Calendar'[Date] ) ), MONTH ( MIN ( 'Calendar'[Date] ) ), 1 ) - 1
RETURN
    CALCULATE (
        [Measure],
        DATESINPERIOD ( 'Calendar'[Date], FirstDateMonth, -3, MONTH ),
        ALL ( 'Calendar'[Date] )
    )

Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



Helpful resources

Announcements
Carousel_PBI_Wave1

2023 Release Wave 1 Plans

Power BI release plans for 2023 release wave 1 describes all new features releasing from April 2023 through September 2023.

Power BI Summit Carousel 2

Global Power BI Training

Make sure you register today for the Power BI Summit 2023. Don't miss all of the great sessions and speakers!

BizApps LATAM 2023

Business Application LATAM Summit 2023

Join the biggest FREE Business Applications Event in LATAM this February.

Power Platform Bootcamp

Global Power Platform Bootcamp

In this bootcamp we will deep-dive into Microsoft’s Power Platform stack with hands-on sessions and labs, delivered to you by experts and community leaders.