Forum Replies Created
Hi, I have some sample data.
Is there a way for me to upload it privately or send you a dropbox link.
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); “])
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.
LASTNONBLANK (tblInvPriceDisc[disc_code], 1 ),
= EARLIER (tblInvSkuMaster[SKU] )
LASTDATE ( tblInvPriceDisc[start_active_dt] )
The correct amount should be ($20N010919), but this is what is chosen ($30N071818)
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.
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?
Thanks, I think that did it.
I did try every way I could think of, was close but just couldn’t finish.
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.
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)
I’ve attached a screenshot of the table.
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.
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.
Not sure if this helps display the issue.
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))
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?