August 8, 2018 at 2:07 pm #10295
I’m looking for a dax formula that does the following
i have a pivot table that looks like this
customer number ,customer name ,Total bx sold
1234 jacks 53
Then i insert a slicer and i filter the total bx sold to the last 12 months
I would like to add another column that would give me total bx sold for the 12 months before the last 12 months
Any help is greatly appreciated
P.S. I’m attaching a sample file
Attachments:You must be logged in to view attached files.August 8, 2018 at 4:15 pm #10301
Is your goal to always compare the last 12 months with the preceding twelve months?August 8, 2018 at 4:31 pm #10302
not necessarily, the slicer in this table happens to be filtered to the last 12 months, what i really want is, that if i would to filter this slicer to the last 8 months the next column would give me the 8 months before that
thanksAugust 8, 2018 at 5:34 pm #10303
- Started: 2
- Replies: 8
- Total: 10
According to me for the previous year, a measure is enough LY =CALCULATE(SUM(Table1[QTY]);SAMEPERIODLASTYEAR('Calendar'[Date]))August 8, 2018 at 5:51 pm #10304
its not previous year its whatever is select in the slicer
your formula will not produce the correct resultsAugust 8, 2018 at 7:53 pm #10305
any answers please ?August 9, 2018 at 5:52 pm #10306
You could make it work by using a disconnected table for your slicer. Disconnected tables and slicers are discussed in “Power Pivot and Power BI”, chapters 12 and 13.
With such a slicer, you would only select one month, and formulas would be adjusted to first create a total for the immediately preceding N months, then another total (measure) that would be for the N months before that.
The second measure would need a “check” to make sure that there were enough months of data to go an additional N months.
Those are the steps to take. Do you have other questions?August 9, 2018 at 6:04 pm #10307
can you please send me back the excel sheet with all those disconnected slicers and tables created so i can see how to do it ?August 9, 2018 at 6:09 pm #10308
Are you using Excel 2016 or 2013 or 2010?August 9, 2018 at 6:21 pm #10309
latest version office 365August 10, 2018 at 1:05 am #10312
hi mr tomallan
i received an email that you replied but for some reason i cant see it here on the forum, can you resend me the attached file please
thanksAugust 10, 2018 at 3:25 am #10313
for some reason i cant download your attachment please resend it
thanksAugust 10, 2018 at 4:16 pm #10319
The latest version.
You select a range with the disconnected slicer (you can choose contiguous slicer buttons, but all that will matter is the “least” and the “greatest”).
The measure “Total Within Selected Range” returns the total quantity within the selected range months (again, it does not matter how many slicer buttons you select, the selected range is determined by the highest and lowest value in the disconnected slicer).
The measure “Total Within Next Range” returns the total quantity within the next “N” number of months where N represents the same number of months starting from the 1 + “greatest” month slicer button selected.
A special case: when you select a range where the next range would exceed the range of available months, the measure “Total Within Next Range” returns blank.
Note: You may notice that I used Power Query to automatically determine the number of available months for the disconnected slicer. Perhaps that level of effort for a beginner with Power Pivot and Power Query is unnecessary. Manually creating a data table for the disconnected slicer on an Excel worksheet and importing it into the data model would work just as well.
As a final comment, you could create a measure with similar logic to get the quantity for the preceding N months.
Attachments:You must be logged in to view attached files.August 12, 2018 at 2:18 pm #10324
thank you very much Mr tomallan that worked perfect!!
Just a few questions
#1 if i create a table manually like you mention in your last paragraph, do i have to list the exact number of months (in this case 31 ) or can i have a bigger number to use in the future as i add more data ?
#2 if i select “0” months in the slicer the” Total Within Next Range” will show the next months data “month #1 ” how can i have it blank out if i select 0 ?
thanksAugust 13, 2018 at 3:13 pm #10327
You can list as many months as you want.
Regarding month 0, you could just exclude it from the slicer (not list month 0 in the disconnected table).
You must be logged in to reply to this topic.