Forum Replies Created

Viewing 15 posts - 1 through 15 (of 25 total)
  • Author
    Posts
  • in reply to: Converting PIVOT TABLES to cube members #18163

    sjhc1177
    Participant
    • Started: 24
    • Replies: 25
    • Total: 49

    Hi, I have some sample data.

    Is there a way for me to upload it privately or send you a dropbox link.

    Thanks.

    in reply to: Power Pivot vs. Power Query #18142

    sjhc1177
    Participant
    • Started: 24
    • Replies: 25
    • Total: 49

    Thanks for the info. I will read up on the great reading.

    Question since you seem to know a lot.

    I would like to write a POWER QUERY statement and tell it to trim not the left 4 characters. But if it sees “$” use the RIGHT 3 of that, or if it sees “PC” use the 2 to the left.

    This is what i currently use:

    = Sql.Database(“SERVER”, “DB0001_160517195223”, [Query=”SELECT LEFT([disc_code], 4) AS [POS DISCOUNT AMOUNT]#(lf)FROM tbldiscount#(lf)GROUP BY LEFT([disc_code], 4); “])

    Thanks

    in reply to: Last Location Worked #18136

    sjhc1177
    Participant
    • Started: 24
    • Replies: 25
    • Total: 49

    Hi, I’ve been applying the suggestion given above to a few situations. But now I have a curve ball so to speak.

    I would like to know the last discount given to an item. But the code above is looking for things based on a sorting logic.

    But see below my issue, see attached image

    This is the calculated column formula.

    =
    CALCULATE (
    LASTNONBLANK (tblInvPriceDisc[disc_code], 1 ),
    TOPN (
    1,
    FILTER (
    tblInvSkuMaster,
    tblInvSkuMaster[SKU]
    = EARLIER (tblInvSkuMaster[SKU] )
    ),
    LASTDATE ( tblInvPriceDisc[start_active_dt] )
    )
    )

    The correct amount should be ($20N010919), but this is what is chosen ($30N071818)

    Attachments:
    You must be logged in to view attached files.
    in reply to: Last Location Worked #18127

    sjhc1177
    Participant
    • Started: 24
    • Replies: 25
    • Total: 49

    Wow, that is great. Thank you so much.

    I can see the application of using that in other areas.

    You have helped me a ton.

     

    Cheers

    in reply to: Last Location Worked #18124

    sjhc1177
    Participant
    • Started: 24
    • Replies: 25
    • Total: 49

    Hi, thanks for your help.

    Last question I have is now I want to be able to subtotal by department. But since it’s a measure I can’t and can’t create a slicer from it either.

     

    What are your thoughts?

    in reply to: Last Location Worked #18123

    sjhc1177
    Participant
    • Started: 24
    • Replies: 25
    • Total: 49

    Thanks, I think that did it.

     

    I did try every way I could think of, was close but just couldn’t finish.

    in reply to: Timeline selection #18095

    sjhc1177
    Participant
    • Started: 24
    • Replies: 25
    • Total: 49

    Thanks so much for your help.

     

    I looked everywhere, but just couldn’t find anything.

     

    The sample data was great. I followed it creating a calendar table and now can select the dates.

     

    Yet again you are a lifesaver. I really can’t thank you enough for all your help on the board/forum.

     

    Steven

    in reply to: Power Query vs Dax for calculations #18074

    sjhc1177
    Participant
    • Started: 24
    • Replies: 25
    • Total: 49

    Hi Tom, thanks for the reply.

    So your suggestion is to use either Dax or M?

    Is one better than the other?

    I’m trying to follow best practices.  For creating new files.  I need the most speed as our files are 100mb and can take 15-20 minutes to poll our local sql server (retail system)

     

    thanks

    in reply to: Distinct Count #15508

    sjhc1177
    Participant
    • Started: 24
    • Replies: 25
    • Total: 49

    I’ve attached a screenshot of the table.

    Attachments:
    You must be logged in to view attached files.
    in reply to: First date in table #15507

    sjhc1177
    Participant
    • Started: 24
    • Replies: 25
    • Total: 49

    Thanks for the post.

    That did the trick.

    Can you explain the FIRSTNONBLANK LINE?

    I have a pitvottable I created with the sales results. I have some transactions where an item was exchanged. So the value of the net transaction is zero. But it still counts as a transaction and therefore the sales associate is in my list. I would like to filter on transactions <>0.

    But you can’t filter measures. IE a months worth of sales for you equal 0 at location 1. So I don’t need you listed. But in location 2 you have sales of $100. So you should be listed.

    How to I get the list to only list non zero NET transactions.

    in reply to: First date in table #15488

    sjhc1177
    Participant
    • Started: 24
    • Replies: 25
    • Total: 49

    Hi, additional thought.

    It would be even better to see all the pay rates for each employee Ranked.

    To see each date we provided you an increase.

     

    thanks

    in reply to: First date in table #15486

    sjhc1177
    Participant
    • Started: 24
    • Replies: 25
    • Total: 49

    Not sure if this helps display the issue.

    Attachments:
    You must be logged in to view attached files.
    in reply to: Distinct Count problem #10408

    sjhc1177
    Participant
    • Started: 24
    • Replies: 25
    • Total: 49

    Sorry to ask another question. I’m also looking to get the same formula for 1 year ago. Here is my old formula:

     

    1YR AGO TRANS# DISTINCT COUNT:=CALCULATE(DISTINCTCOUNT(‘SALES TABLE'[TRANS#]),’SALES TABLE'[UNITS SOLD]>0,dateadd(dCalendar[DATES],-364,DAY))-CALCULATE(DISTINCTCOUNT(‘SALES TABLE'[TRANS#]),’SALES TABLE'[UNITS SOLD]<0,dateadd(dCalendar[DATES],-364,DAY))

     

    Steven

    in reply to: Distinct Count problem #10407

    sjhc1177
    Participant
    • Started: 24
    • Replies: 25
    • Total: 49

    Wow, thanks a lot. That totally did the trick.

     

    I’ve never used “ADDCOLUMNS”, I must read up on it.

    I thought “SUMX” was to be avoided? Something about being slow over a large system?

     

    Steven

    Cheers

    in reply to: Distinct Count problem #10403

    sjhc1177
    Participant
    • Started: 24
    • Replies: 25
    • Total: 49

    See attached image that didn’t display in the post.

    Attachments:
    You must be logged in to view attached files.
Viewing 15 posts - 1 through 15 (of 25 total)