# First Date for Each Product Key

### Dotaz

• I have a Product  and Date Dimension. How to get First Date for each Product. For example as shown below

Product    DATE

ABC

01/01/2011

01/02/2011

01/13/2012

XYZ

10/11/2009

10/12/2010

01/01/2011

I need to the 01/01/2011 for Product ABC or 10/11/2009 for Product XYZ. Any Ideas are appreciated...Thanks in Advance...

Words offer the means to meaning, and for those who will listen, the enunciation of truth - V for Vendetta.

12. dubna 2012 2:31

### Odpovědi

• Hi Sandeep

So are you able to get the LOOKUPVALUE correctly, but you are missing the last step of the calculation?  If this is the case, and what you need is the date between the min date per category and the max date on the fact table, then you can use an expression like the following:

```IF(
CALCULATE( COUNTROWS( FactInternetSales ) ) ,
CALCULATE(COUNTROWS(DimDate),
DATESBETWEEN(
DimDate[FullDateAlternateKey],
LOOKUPVALUE(
DimDate[FullDateAlternateKey],
DimDate[DateKey],
MIN(FactInternetSales[OrderDateKey])
),
MAX(DimDate[FullDateAlternateKey])
)
), BLANK()
)```

Javier Guillen
http://javierguillen.wordpress.com/

• Označen jako odpověď 13. dubna 2012 20:11
13. dubna 2012 18:47
• Hi Sandeep

What you can do in that case is a slightly modification to the calculation, as shown below.  That would give the output you need:

```IF(
CALCULATE( COUNTROWS( FactInternetSales ) ) ,
INT(
MAX(DimDate[FullDateAlternateKey])
-
LOOKUPVALUE(
DimDate[FullDateAlternateKey],
DimDate[DateKey],
MIN(FactInternetSales[OrderDateKey])
)
)

, BLANK()
)```

Javier Guillen
http://javierguillen.wordpress.com/

• Označen jako odpověď 17. dubna 2012 20:18
17. dubna 2012 19:00

### Všechny reakce

• Hi san463,

You can use the new DAX "LOOKUPVALUE" for this, which in this case is necessary as the call needs to get the minimum date value for a product (dimension # 1), through its relationship with the facts get the value on the date table (dimension # 2).  As such, an expression like the one below should be used:

`LOOKUPVALUE(DimDate[FullDateAlternateKey], DimDate[DateKey], MIN(FactInternetSales[OrderDateKey])  )`

Javier Guillen
http://javierguillen.wordpress.com/

13. dubna 2012 3:40
• Javier:

Thank you very much for the reply. I tried working on this but in vain. I used the following expression as the measure but was unable to get the value:

LOOKUPVALUE('Activity Date'[FullDate],'Activity Date'[DateSK],MIN('Demand Planning'[ActivityDateSK])

In the above:

'Activity Date' is the date dimension. 'Activity Date'[Full Date] is the FullDateTime column of the date dimension. 'Activity Date'[DateSK] is the surrogate key of th Date Dimension. 'Demand Planning' is the Fact Table and 'Demand Planning'[ActivityDateSK] is the activity date surrogate key in the fact table.

My ulterior motive is to get the date difference between min date and substract it from Today() date.

For Example: FinalDateDiff:= Today() -  LOOKUPVALUE('Activity Date'[FullDate],'Activity Date'[DateSK],MIN('Demand Planning'[ActivityDateSK]).

Your inputs are much appreciated. Thanks in Advance.

Thanks,

Sandeep

Words offer the means to meaning, and for those who will listen, the enunciation of truth - V for Vendetta.

13. dubna 2012 17:38
• Hi Sandeep

So are you able to get the LOOKUPVALUE correctly, but you are missing the last step of the calculation?  If this is the case, and what you need is the date between the min date per category and the max date on the fact table, then you can use an expression like the following:

```IF(
CALCULATE( COUNTROWS( FactInternetSales ) ) ,
CALCULATE(COUNTROWS(DimDate),
DATESBETWEEN(
DimDate[FullDateAlternateKey],
LOOKUPVALUE(
DimDate[FullDateAlternateKey],
DimDate[DateKey],
MIN(FactInternetSales[OrderDateKey])
),
MAX(DimDate[FullDateAlternateKey])
)
), BLANK()
)```

Javier Guillen
http://javierguillen.wordpress.com/

• Označen jako odpověď 13. dubna 2012 20:11
13. dubna 2012 18:47
• Javier...I thank you very much for your code...It works like a charm...I want to do some modifications to the code...Presently the code gets me the date difference between minimum of date dimension and the present day...What i am trying to do is get the date difference between  minimum of date for the fact table and  present day..For example:

Product  Activity Date   Date Difference ('01-03-2011' - ' 01-01-2011')

ABC

01-01-2011            2

01-02-2011            1

01-03-2011            0

The Present code gives me the date difference but it selects the minimum date of the date dimension. For example lets assume the minimum date of date dimension is 12-31-2011. The data is as below.

Product  Activity Date   Date Difference

ABC

01-01-2011            1

01-02-2011            2

01-03-2011            3

I know i have to do some changes in the getting the minimum date in the lookup value...am i going in the right path...Please let me know your thoughts...

Words offer the means to meaning, and for those who will listen, the enunciation of truth - V for Vendetta.

13. dubna 2012 21:00
• Hi Sandeep

What you can do in that case is a slightly modification to the calculation, as shown below.  That would give the output you need:

```IF(
CALCULATE( COUNTROWS( FactInternetSales ) ) ,
INT(
MAX(DimDate[FullDateAlternateKey])
-
LOOKUPVALUE(
DimDate[FullDateAlternateKey],
DimDate[DateKey],
MIN(FactInternetSales[OrderDateKey])
)
)

, BLANK()
)```

Javier Guillen
http://javierguillen.wordpress.com/

• Označen jako odpověď 17. dubna 2012 20:18
17. dubna 2012 19:00
• Javier:

I thank you very much for your patience in answering my questions. I was wrapping my head around your formula for two days to figure the following but in no vail hence i am contacting you back....The formula works splendid at the summary level but at leaf level it gives me 0's. I have worked this formula in ADV Works too but there also it gives me 0's. The current output is as below:

Product    Date                          DateDiff

ABC                                               30

01-01-2011                      0

01-03-2011                      0

01-05-2011                     0

If i were to put only fulldate in the table with a product, i get all 0's for every date, and at the summary level i get the date difference value. Please let me know i understood the formula correctly. Thanks a lot in advance..

Words offer the means to meaning, and for those who will listen, the enunciation of truth - V for Vendetta.

19. dubna 2012 0:32