Forum Discussion
2 DAX Questions
Hi, I am trying to figure out where I am going wrong with the below DAX Expression
=IF(AND(YEAR([Demand Sent]=YEAR(TODAY())),WEEKNUM([Demand Sent]=WEEKNUM(TODAY())),FORMAT([Demand Sent],"ddd"),""))
[Demand Sent] is a field that is either a date, or is blank. I am trying to get the dax expression to return "Mon" or "Tue" or "Wed" etc. if the date in this field is from the current week (Monday - Sunday) or return blank if its not. the last peice, FORMAT([Demand Sent],"ddd"),"")) is giving me problems.
The second question is how can I use something comparable to vlookup in dax? I currently am using a vlookup to return a formula to then be applied to the current column.. this works in excel but not sure how to do it, or if its possible, to do this in dax..
=TODAY()-OFFSET($A22,0,VLOOKUP($AT22,BucketIDTable!G:I,3,FALSE)-1,1,1)
Any help with either question would be greatly appreciated!
Hi BGuttmann,
If you need check the year, you can use YEAR function.
Column = IF(WEEKNUM(Table1[Date])=WEEKNUM(TODAY())&&YEAR(Table1[Date])=YEAR(TODAY()),,SWITCH(WEEKDAY(Table1[Date]),1,"Sunday",2,"Monday",3,"Tues day",4,"Wednesday",5,"Thursday",6,"Friday",7,"Satu rday"),"Other Week") And the week is start from Sunday.
Regards,
Charlie Liao
9 Replies
Power Pivot is very different to Excel, and it is very common for Excel Pros to take an approach like this. But I am almost certain there is a better way to do what you want. It looks like you only have a single table. If you want to know day names, the. You should load a calendar table, create a relationship, and use the day name from the calendar table. http://exceleratorbi.com.au/power-pivot-calendar-tables/
Your VLOOKUP question is also probably the wrong question. Can you describe your data and what you want to achieve?
I see - okay, so is the "calendar table" just adding a column? I dont see how to add a new table without importing data to powerpivot.
as to the vlookup -- what i am trying to do is take value (what I am calling a bucket) and then look that up in a vlookup.. which then tells me which column to subtract today() from.. basically, I want to see how long a file has been in a certain "bucket" for..
to determine a bucket, I am using a formula to give me a string of numbers,, 1 = the date is populated, 0 = the date is not..
so, 11111001110000 - lets say is bucket
I would use vlookup to find that 11111001110000 is int he bucket "Document Generated - Needs Review"
Then, at this stage, where my question stemmed from, I am using vlookup to then lookup Document Generated - Needs review and return the formula which would be TODAY() minus the column "Document Generated"
Okay - i saw how to add the Calendar table and went ahead and did so. Can you tell me what the formula would be to check if a column date is from the current week and if so, return monday, tues,wed, thur,fri,sat or sun?
Hi BGuttmann,
To return weekday name if the date belong to current week, you can use the DAX below.
Column = IF(WEEKNUM(Table1[Date])=WEEKNUM(TODAY()),SWITCH(WEEKDAY(Table1[Date]),1,"Sunday",2,"Monday",3,"Tuesday",4,"Wednesday",5,"Thursday",6,"Friday",7,"Saturday"),"Other Week")Regards,
Charlie Liao
