Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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
Any help is appreciated.
Regards,
Jake
Solved! Go to Solution.
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.
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/
@v-chuncz-msft, I appreciate the reply, but I am still struggling to find a solution by piecing these all together.
Anybody else have any suggestions?
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.
Hi,
I'm also struggling to get a chart that displays the last 12 months of Sales YTD and Sales (Rolling Total).
Can you help me? Is there any solution?
Best regards
LMFC
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!
bump...
User | Count |
---|---|
125 | |
106 | |
99 | |
63 | |
62 |
User | Count |
---|---|
135 | |
116 | |
101 | |
71 | |
61 |