cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
jcarville Established Member
Established Member

Show sales for last 'X' months and Same Period Last Year

Hi,

 

I have been looking at showing sales for the last 'X' months using the brilliant guide from SQLJason. All good so far.

 

My issue is I would like to also show the sales for the same period last year based on the month I have selected, and include the logic for last 'X' months i.e. in the example below becuase I have selected Dec 2017, only the value for Dec 2016 is showing in the table. I would like my table to show the figures as shown in red going back the same 'X' months and showing BLANK() if no data is available.

 

The file can be seen here: https://drive.google.com/open?id=1GcYElXLi54hupoFYEeNFyjBOlyvjQlAZ

 

Capture.PNG

 

Any help is appreciated.

 

Regards,

Jake

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
SqlJason Member
Member

Re: Show sales for last 'X' months and Same Period Last Year

This should work

 

Sales Last Year = var S_Mno = CALCULATE(MAX(Sales[MonthYearNo]), ALL('Date'))
var S_Mno_LY = IF(NOT(ISBLANK([Sales (last n months)])), INT(INT(LEFT(S_Mno, 4))-1 & RIGHT(S_Mno, LEN(S_Mno)-4)) )
var Sal_LY = CALCULATE(SUM(Sales[Sales]), FILTER(ALL(Sales[MonthYearNo], Sales[MonthYear]), 'Sales'[MonthYearNo] = S_Mno_LY), ALL('Date'))
return Sal_LY

 

Note that I am trying to find the Month number of previous year and then finding the sales for that month number. 

View solution in original post

7 REPLIES 7
jcarville Established Member
Established Member

Re: Show sales for last 'X' months and Same Period Last Year

bump...

Community Support Team
Community Support Team

Re: Show sales for last 'X' months and Same Period Last Year

@jcarville,

 

You may try applying virtual relationship in DAX and also implement a custom DAX formula for time intelligence function.

http://community.powerbi.com/t5/Desktop/Relational-Date-Filter-By-Other-Date-How-To/td-p/298373

https://www.sqlbi.com/articles/time-intelligence-in-power-bi-desktop/

Community Support Team _ Sam Zha
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
jcarville Established Member
Established Member

Re: Show sales for last 'X' months and Same Period Last Year

@v-chuncz-msft, I appreciate the reply, but I am still struggling to find a solution by piecing these all together.

jcarville Established Member
Established Member

Re: Show sales for last 'X' months and Same Period Last Year

Anybody else have any suggestions?

SqlJason Member
Member

Re: Show sales for last 'X' months and Same Period Last Year

This should work

 

Sales Last Year = var S_Mno = CALCULATE(MAX(Sales[MonthYearNo]), ALL('Date'))
var S_Mno_LY = IF(NOT(ISBLANK([Sales (last n months)])), INT(INT(LEFT(S_Mno, 4))-1 & RIGHT(S_Mno, LEN(S_Mno)-4)) )
var Sal_LY = CALCULATE(SUM(Sales[Sales]), FILTER(ALL(Sales[MonthYearNo], Sales[MonthYear]), 'Sales'[MonthYearNo] = S_Mno_LY), ALL('Date'))
return Sal_LY

 

Note that I am trying to find the Month number of previous year and then finding the sales for that month number. 

View solution in original post

Highlighted
jcarville Established Member
Established Member

Re: Show sales for last 'X' months and Same Period Last Year

Thanks for following up @SqlJason, that's a really creative way of getting the sales based on the month number. I appreciate your help, and am a big fan of your blog!

Davidrra Visitor
Visitor

Re: Show sales for last 'X' months and Same Period Last Year

@SqlJason

I have been also looking at your guide and it work great, however I am struggling to get a chart that displays the last 12 months of Sales YTD and Sales (Rolling Total) 

Wondering if you could give me any suggestions 
Thanks in advance

Helpful resources

Announcements
New Kudos Received Badges Coming

New Kudos Received Badges Coming

Kudos to you if you earned one of these! Check your inbox for a notification.

Microsoft Implementation for Communities Wins Award

Microsoft Implementation for Communities Wins Award

Learn about the award-winning innovation that was implemented across Microsoft’s Business Applications Communities.

Power Platform World Tour

Power Platform World Tour

Find out where you can attend!

Top Kudoed Authors (Last 30 Days)
Users online (4,026)