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

The Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.

Reply
Anonymous
Not applicable

Getting Last 6 Month Sales

I want to get the last 6 months sales ending on the last day of last month from the current date. Following code is what I use. Is there any better suggestion to improve this? 

 

6M pri sales value =
CALCULATE (
    SUM ( FactPrimarySales[NetValue] ),
    DATESINPERIOD (
        DimDate[Date],
        CALCULATE (
            LASTDATE ( DimDate[Date] ),
            DimDate[MonthNo]
                = MONTH ( TODAY () ) - 1,
            DimDate[Year] = YEAR ( TODAY () )
        ),
        -6,
        MONTH
    )
)

 

 

2 ACCEPTED SOLUTIONS
amitchandak
Super User
Super User

@Anonymous , Try a measure like this with help from date table

 

Rolling 6 = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date ],eomonth(MAX('Date'[Date ]),-1),-6,MONTH))

Full Power BI Video 20 Hours YouTube
Microsoft Fabric Series 60+ Videos YouTube
Microsoft Fabric Hindi End to End YouTube

View solution in original post

Ashish_Mathur
Super User
Super User

Hi,

If today is December 25, 2020, then i assume that "last day of last month from the current date" should mean 6 months ended November 30, 2020.  If my assumption is correct, then try this measure:

=calculate(sum(Sales[Sales Amount]),datesbetween(calendar[date],EDATE(EOMONTH(today(),-1)+1,-6),EOMONTH(today(),-1)))

Hope this helps.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

View solution in original post

2 REPLIES 2
Ashish_Mathur
Super User
Super User

Hi,

If today is December 25, 2020, then i assume that "last day of last month from the current date" should mean 6 months ended November 30, 2020.  If my assumption is correct, then try this measure:

=calculate(sum(Sales[Sales Amount]),datesbetween(calendar[date],EDATE(EOMONTH(today(),-1)+1,-6),EOMONTH(today(),-1)))

Hope this helps.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
amitchandak
Super User
Super User

@Anonymous , Try a measure like this with help from date table

 

Rolling 6 = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date ],eomonth(MAX('Date'[Date ]),-1),-6,MONTH))

Full Power BI Video 20 Hours YouTube
Microsoft Fabric Series 60+ Videos YouTube
Microsoft Fabric Hindi End to End YouTube

Helpful resources

Announcements
Feb2025 Sticker Challenge

Join our Community Sticker Challenge 2025

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

Jan NL Carousel

Fabric Community Update - January 2025

Find out what's new and trending in the Fabric community.