Home Forums Power Pivot Previous from column to the left dax

This topic contains 16 replies, has 3 voices, and was last updated by  tomallan 9 months, 2 weeks ago.

Viewing 15 posts - 1 through 15 (of 17 total)
• Author
Posts
• #10295

ZZZZZ
Participant
• Started: 5
• Replies: 21
• Total: 26

HI

question

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.
#10301

tomallan
Keymaster
• Started: 7
• Replies: 2554
• Total: 2561

Is your goal to always compare the last 12 months with the preceding twelve months?

#10302

ZZZZZ
Participant
• Started: 5
• Replies: 21
• Total: 26

hi

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

thanks

#10303

stasinek
Participant
• Started: 4
• Replies: 13
• Total: 17
```According to me for the previous year, a measure is enough
LY =CALCULATE(SUM(Table1[QTY]);SAMEPERIODLASTYEAR('Calendar'[Date]))```
#10304

ZZZZZ
Participant
• Started: 5
• Replies: 21
• Total: 26

Hi stasinek

its not previous year its whatever is select in the slicer

your formula will not produce the correct results

#10305

ZZZZZ
Participant
• Started: 5
• Replies: 21
• Total: 26

#10306

tomallan
Keymaster
• Started: 7
• Replies: 2554
• Total: 2561

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?

#10307

ZZZZZ
Participant
• Started: 5
• Replies: 21
• Total: 26

Hi

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 ?

#10308

tomallan
Keymaster
• Started: 7
• Replies: 2554
• Total: 2561

Are you using Excel 2016 or 2013 or 2010?

#10309

ZZZZZ
Participant
• Started: 5
• Replies: 21
• Total: 26

2016

#10312

ZZZZZ
Participant
• Started: 5
• Replies: 21
• Total: 26

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

thanks

#10313

ZZZZZ
Participant
• Started: 5
• Replies: 21
• Total: 26

thanks

#10319

tomallan
Keymaster
• Started: 7
• Replies: 2554
• Total: 2561

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.

Tom

###### Attachments:
You must be logged in to view attached files.
#10324

ZZZZZ
Participant
• Started: 5
• Replies: 21
• Total: 26

hi

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 ?

thanks

#10327

tomallan
Keymaster
• Started: 7
• Replies: 2554
• Total: 2561

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).

Viewing 15 posts - 1 through 15 (of 17 total)

You must be logged in to reply to this topic.