Getting the Date field from my Sales table that only has a Surrogate key for the date

DAX DAX Calculations
1

Hi,
I am hoping this is simple. I want to get the earliest date in the sales table. The DAX blow will get me to the minimum sk_Date (20180101) in the sales table. What I am looking for is to return the value from the date table that has this sk_Date

DAX
VAR FirstDayWithData =
CALCULATE (
MIN ( ‘Sales’[sk_Date]),
REMOVEFILTERS ()
)
Date Table fields
[sk_Date] - integer
[Date] - Date format

Is there an easy solution to this such as related. Struggling to see an example for this.
Cheers
E

2

Hi @ells

Can you please share your sample pbix file and what is the output you are expecting

Thanks
Mukesh

3

@ells,

See if this does the trick for you:

Retrieve =

VAR FirstDayWithData = 
CALCULATE( MIN( 'Sales'[sk_Date] ), REMOVEFILTERS(  ) ) 

RETURN
LOOKUPVALUE(
    Dates[Date],
    Dates[sk_Date],
    FirstDayWithData
)
  • Brian
1 Like
4

@ells Another suggestion:

VAR FirstDayWithData =
CALCULATE (
    MIN ( 'Sales'[sk_Date] ),
    SUMMARIZE ( Sales, 'Sales'[sk_Date], Dates[Date] ),
    REMOVEFILTERS ()
)

.

VAR FirstDayWithData =
MINX (
    CALCULATETABLE (
        SUMMARIZE ( Sales, 'Sales'[sk_Date], Dates[Date] ),
        REMOVEFILTERS ( )
    ),
    'Sales'[sk_Date]
)
5

Thanks all.
went with the lookupvalue as this is easier for me to read and understand.

Cheers
E