I am trying to calculate total sales by month year (columns in a matrix) and product (rows) based on two slicer values (year and month) that are synced across multiple pages. All other pages display visuals at the month granularity however on this particular page I need a dynamic rolling 12 months based on the slicers values. I have tried to edit the interaction between the slicers and matrix so the slicers do not actually filter the matrix (to just one month/year) and I have tried unsuccessfully to grab the selected values of the slicers and use those as variables to calculate the previous twelve months worth of sales using datesbetween. At this point I am just going in circles trying things that I have already tried 2-3 times before. Any suggestions?
That must be really annoying, would you be able to post sample pbix file for reference please
Not 100% sure this is what you’re looking for but it should get the party started
- Table A, works with the date slicer on Year & Month
- Table B, doesn’t interact with the date slicer on Year & Month but has a fixed filter in the Filter Pane
Created this measure for Table A
Sales in last 12M = VAR myMonthOffset = CALCULATE( MAX( Dates[MonthOffset] ), ALLSELECTED( Dates[Month & Year] ), ALLEXCEPT( Dates, Dates[Month & Year] ) ) RETURN CALCULATE( [Total Sales], FILTER( ALL(Dates), Dates[MonthOffset] >= myMonthOffset -12 && Dates[MonthOffset] <= myMonthOffset ) )
You can find the M code for the Date table including Offsets here:
I hope this is helpful. Here’s my sample file.
eDNA - Previous 12 months sales.pbix (451.3 KB)
Thank you very much Melissa and NajahS for your willingness to assist me!
Melissa, using your example file I can recreate my issue. I am needing 12 Month & Years as columns going across the top of the matrix. When I use your example, I get something similar to what I was experiencing…matrix A gets filtered to just a single month. Here is an example of what the desired output should look like (the row values are obviously not correct). To get the 12 Month & Year column headers to go across the top of he matrix I had to select the values in the slicer. The challenge (for me) is showing the previous 12 Month & Year columns based on a single slicer selection.
Here is what I use for my Rolling Twelve Months in all of my models. I just took @Melissa model and added another measure. Let me know if this works. I also added a date slicer at the top for the Date range you want to see. this works much better in my opinion that the slicer you have in your model.
Just looked and my solution is very similar to @Melissa, just with a different slicer solution. Both of our measures bring back same results.
Total Sales TTM = CALCULATE ( [Total Sales], DATESINPERIOD( Dates[Date], MAX( Dates[Date]), -12, MONTH))
EDNA - Previous 12 months sales Jarrett.pbix (454.2 KB)
Hey Jarrett, thanks for taking a look at this for me!
I agree with your date slicer comment however in this case the report/dashboard is used to perform customer reviews so users select various slicer values on page 1 of the report (page 1 is nothing but slicers) and those values sync to all pages (10+ pages). All pages are at the month level except this page and I think it would be confusing to the user to either a.) have an additional slicer on one of the report pages when the other report pages don’t or b.) have month and year slicers that apply to all but one page and an additional date range slicer that only applies to one page. Does that make sense?
That makes sense. You could add an additional slicer like I had in my solution to page 1 to let the user know that slicer only affects results on that page. You could also use option B from your reply to my solution.
Agreed, however I have to think there is a DAX solution to this issue.
There’s an alternative but that (again) does require a separate Slicer…
I’ve created a separate table for the Month and Year selection. The Month & Year field from this table is then placed within the Slicer on the report page.
MonthSelector = SELECTCOLUMNS( Dates, "Month & Year", Dates[Month & Year], "MonthInYear", Dates[MonthnYear] )
Next updated the measure:
Sales in last 12M v2 = VAR myMonthSelection = CALCULATE( MAX( MonthSelector[MonthInYear] ), ALLSELECTED( MonthSelector[Month & Year] )) VAR myMonthOffset = LOOKUPVALUE( Dates[MonthOffset], Dates[MonthnYear], myMonthSelection) VAR MonthRange = GENERATESERIES( myMonthOffset -11, myMonthOffset, 1 ) RETURN IF( VALUES( Dates[MonthOffset] ) IN MonthRange, CALCULATE( [Total Sales], FILTER( ALL(Dates), Dates[MonthOffset] >= SELECTEDVALUE( Dates[MonthOffset]) -12 && Dates[MonthOffset] <= SELECTEDVALUE( Dates[MonthOffset]) ) ), BLANK() )
The visual now displays 12 months of data.
I hope this is helpful. Here’s the updated file.
EDNA - Previous 12 months sales v2.pbix (449.0 KB)
Melissa, I can make this work…thank you very much! Just curious if it is possible to generate MonthSelector as a measure rather than table? This is actually a SSAS tabular model and I can add it as a table but would prefer not to if there is another options.
Thanks again for your help (Jarrett as well)!
Hi Troy, Power BI doesn’t support using measures as slicers (at this time), the disconnected table trick is the only alternative I’m aware of.