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 2 months, 1 week ago.

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

    ZZZZZ
    Participant
    • Started: 4
    • Replies: 18
    • Total: 22

    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: 2517
    • Total: 2524

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

    #10302

    ZZZZZ
    Participant
    • Started: 4
    • Replies: 18
    • Total: 22

    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: 2
    • Replies: 8
    • Total: 10
    According to me for the previous year, a measure is enough
    LY =CALCULATE(SUM(Table1[QTY]);SAMEPERIODLASTYEAR('Calendar'[Date]))
    #10304

    ZZZZZ
    Participant
    • Started: 4
    • Replies: 18
    • Total: 22

    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: 4
    • Replies: 18
    • Total: 22

    any answers please ?

    #10306

    tomallan
    Keymaster
    • Started: 7
    • Replies: 2517
    • Total: 2524

    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: 4
    • Replies: 18
    • Total: 22

    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: 2517
    • Total: 2524

    Are you using Excel 2016 or 2013 or 2010?

    #10309

    ZZZZZ
    Participant
    • Started: 4
    • Replies: 18
    • Total: 22

    2016

    latest version office 365

    #10312

    ZZZZZ
    Participant
    • Started: 4
    • Replies: 18
    • Total: 22

    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: 4
    • Replies: 18
    • Total: 22

    for some reason i cant download your attachment please resend it

     

    thanks

    #10319

    tomallan
    Keymaster
    • Started: 7
    • Replies: 2517
    • Total: 2524

    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.

    Tom

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

    ZZZZZ
    Participant
    • Started: 4
    • Replies: 18
    • Total: 22

    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: 2517
    • Total: 2524

    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.