Forum Discussion

grggmrtn's avatar
grggmrtn
Icon for Post Patron rankPost Patron
6 years ago

Calculating MIN(Date) with data from three different tables...

I have three tables: Fact_Keys, Dim_Details, Dim_Dates Fact_Keys has key columns to the two dimension tables: SK_Details and SK_Date. Dim_Details has columns SK_Details, ServiceID, ServiceStatus, B...
Need Help
grggmrtn's avatar
grggmrtn
Icon for Post Patron rankPost Patron
6 years ago

I'm sorry amitchandak but I can't quite figure out what you're trying to tell me. The names you use in your example aren't the ones I gave in my description, and you refer to two tables while I spoke of three?

amitchandak's avatar
amitchandak
Icon for Super User rankSuper User
6 years ago

Try a new column in Dim_Details

 

StartDate  =
if(
minx(filter(Fact_Keys, Fact_Keys[SK_Details]=Dim_Details[SK_Details]),Fact_Keys['SK_Date']) <date(2015,01,01)
,Dim_Details [BackupDate],minx(filter(Fact_Keys, Fact_Keys[SK_Details]=Dim_Details[SK_Details]),Fact_Keys['SK_Date']) )

 

  • grggmrtn's avatar
    grggmrtn
    Icon for Post Patron rankPost Patron
    6 years ago

    amitchandak 

    Hmm, this actually looks like is almost works. The resulting format is a bit off though, with some of the results looking like 20160105 (aka january 5, 2016) and others looking like 42374, 2958465, 42375.... The new column has a decimal datatype and changing it only brings error (system format exception)...

     

    Maybe because your code doesn't refer to DIM_Date[Date] at all? The output is the key, not the date itself...

     

    • amitchandak's avatar
      amitchandak
      Icon for Super User rankSuper User
      6 years ago

      Get the min date in the column. Get its date using another formula from date table and compare it with min dates and then again apply for logic get BackupDate