# Power BI Exchange

View Only

## Calculating First Sale Date Dynamically and Statically

• #### 1.  Calculating First Sale Date Dynamically and Statically

Silver Contributor
Posted Nov 09, 2018 11:04 AM
​Hello,

I am trying to calculate first sale date dynamic to a date slicer and the following works:

First Sales Date =
VAR
FirstSale= FIRSTDATE(Sales[Purchase Date])
Return
FirstSale

This creates a first sale date in the selected date slicer. Example: if the year selected is 2016 the earliest first sale date you will find is 1/1/2016.

Now I need to create the first sale date regardless of the date slicer. I know I need a calculate function and possibly a filter function, but I can't quite get it right. Example: if the year selected in the slicer is 2016 it will still calculate the first sale of say 1/1/2015.

I have tried:

First Sales Date =
VAR
FirstSale= FIRSTDATE(Sales[Purchase Date])
Return
CALCULATE(FirstSale, ALL(Sales))

AND

First Sales Date =
VAR
FirstSale= FIRSTDATE(Sales[Purchase Date])
Return
CALCULATE(FirstSale, ALL(Dates))

Any ideas?

------------------------------
Malori Meyer
Analyst
9798249358
------------------------------

• #### 2.  RE: Calculating First Sale Date Dynamically and Statically

Silver Contributor
Posted Nov 09, 2018 11:54 AM
Edited by Malori Meyer Nov 09, 2018 11:56 AM

------------------------------
Malori Meyer
Analyst
9798249358
------------------------------

• #### 3.  RE: Calculating First Sale Date Dynamically and Statically

Top Contributor
Posted Nov 09, 2018 05:09 PM
Hi,

Hopefully this will solve your issue;

First Sales Date = FIRSTDATE(Sales[Purchase Date])

First Sales Date1 = CALCULATE([First Sales Date], ALL(Dates))

Regards,

------------------------------
Hasham Bin Niaz
Sr. BI Consultant
Karachi, Pakistan
------------------------------

• #### 4.  RE: Calculating First Sale Date Dynamically and Statically Best Answer

Gold Contributor
Posted Nov 12, 2018 01:55 AM
Hi @Malori Meyer

The issue with your DAX is that, the Variable "FirstSale" would have been already calculated with the context filters in place (that is the Date in slicer). The CALCULATE after the RETURN will not again open up the context for the variable already calculated. You should do the opening in the variable itself.
Please see the following codes:

1.       The way you have calculated

```First Sale Date 1 =
VAR firstsale =
FIRSTDATE ( 'Sales Table'[Sale Date] )
RETURN
CALCULATE ( firstsale, ALL ( 'Sales Table' ) )```

2. DAX with the opening of the context filter in the variable itself

```First Sale Date 2 =
VAR firstsale =
CALCULATE(FIRSTDATE ( 'Sales Table'[Sale Date] ),ALL('Sales Table'))
RETURN
firstsale```

Hope this helps. PFA the pbix file also for reference.

------------------------------
Gopa Kumar S

------------------------------

Attachment(s)

First Sale Date.pbix   46K 1 version