Forum Discussion

BGuttmann's avatar
BGuttmann
Regular Visitor
9 years ago
Solved

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"),"...
  • v-caliao-msft's avatar
    v-caliao-msft
    9 years ago

    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,"Tuesday",4,"Wednesday",5,"Thursday",6,"Friday",7,"Saturday"),"Other Week")

     

    And the week is start from Sunday.

     

    Regards,

    Charlie Liao

MattAllington's avatar
MattAllington
Community Champion
9 years ago

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?

BGuttmann's avatar
BGuttmann
Regular Visitor
9 years ago

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"