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

[grggmrtn]

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, BackupDate.

Dim_Date has columns SK_Date, Date.

I need to find out the MIN(Date) for each [ServiceID], and if that date = 1 january 2015, then it takes [BackupDate] instead. The result will be called StartDate.

If this was a flat table I'd just do something like this:

StartDate = 
var currentStartDate = 
    FORMAT(
    CALCULATE(
        MIN('Fact'[Date]);
        ALL('Fact');
        'Fact'[ServiceId] = EARLIER('Fact'[ServiceId])
    );"dd-mm-yyyy")
RETURN
    IF(
        currentStartDate = "01-01-2015"; 
        FORMAT('Fact'[BackupDate];"dd-mm-yyyy");
        currentStartDate
    )

I need to isolate that date in order to count how many services were started in a given time frame (and will also need the stop date, but that's just MAX instead of MIN...)

Any ideas?

[amitchandak]

You should able to create a new column in service table and can get data from other tables like the example given below

New column in Table 1 = maxx(filter(table2,table1[customer] = table2[customer] && table2[option]="construction",table2[value])

New column in Table 1 = maxx(filter(table2,table1[Attribute] = table2[name] && table1[project] = table2[project],table[name])

Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution. In case it does not help, please provide additional information and mark me with @
Thanks. My Recent Blog -
Winner-Topper-on-Map-How-to-Color-States-on-a-Map-with-Winners , HR-Analytics-Active-Employee-Hire-and-Termination-trend
Power-BI-Working-with-Non-Standard-Time-Periods And Comparing-Data-Across-Date-Ranges

Connect on Linkedin

[grggmrtn]

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]

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]

@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]

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

[vanessafvg]

@grggmrtn  is it possible to provide some dummy data(n text form)? also how are the relationships set up between these tables

[grggmrtn]

Hey @vanessafvg I'll try... The actual tables are huge so I'm not even attempting to do a 1:1 dummy, and I might be breaking the logic by trying to replicate this (!)

Logic is, that each ServiceID should have a list of consecutive dates. It's the first and the last of these that I need to isolate.

The two dimensions are related to the fact table through their respective key columns, 1 to many.

Dim_Details

SK_Details	ServiceID	ServiceStatus	BackupDate
1	9	Doesn't matter	5. januar 2016
2	9	Doesn't matter	5. januar 2016
3	20	Doesn't matter	6. januar 2016
4	20	Doesn't matter	6. januar 2016
5	20	Doesn't matter	23. juni 2016
6	27	Doesn't matter	22. februar 2016
7	27	Doesn't matter	16. juni 2016
8	27	Doesn't matter	22. februar 2016
9	27	Doesn't matter	22. februar 2016

Dim_Date

SK_Date	Date
1	1. januar 2000
2	31. januar 2000
3	29. februar 2000
4	31. marts 2000
5	30. april 2000
6	31. maj 2000
7	30. juni 2000
8	31. juli 2000
9	21. august 2000

And The fact table just has all values from SK_Details and SK_Date

Hope this makes sense??