# How can I do a nested date calculation with DAX?

• ### Question

• Hi,

I have a CSV file that looks like below

JobOrderId CandidateId From_StatusID From_StatusDate To_StatusID
1234 5000 1 07/01/2015 2
1234 5000 2 08/01/2015

I've read this file in PowerPivot and I need to calculate the time in weeks between the two dates for the same JO, Candidate and when the to_StatusId = From_StatusID. In other words, the number of weeks that took from going From Status "1" to Status "2".

Is it possible to do something like this using DAX? Do I need to create a calculated Column?

I appreciate the help.

Sunday, August 9, 2015 11:12 PM

• You could also consider this a job to be done before loading your data into Power Pivot, using it's ETL-tool Power Query. This would be your code then - just replace "YourTable" by the Name of your table & choose the "Load to Data Model"-Option:

let
Source = Excel.CurrentWorkbook(){[Name="YourTable"]}[Content],
SelfJoin = Table.NestedJoin(Source,{"JobOrderId", "To_StatusID", "CandidateId"},Source,{"JobOrderId", "From_StatusID", "CandidateId"},"NewColumn"),
ExpandStatusChangeDate = Table.ExpandTableColumn(SelfJoin, "NewColumn", {"From_StatusDate"}, {"From_StatusDate.1"}),
CalcWeeks = Table.AddColumn(ExpandStatusChangeDate, "Weeks", each ([From_StatusDate.1]-[From_StatusDate])/7),
ChangeType = Table.TransformColumnTypes(CalcWeeks,{{"Weeks", type number}})

in
ChangeType

Imke

• Proposed as answer by Thursday, August 13, 2015 6:45 AM
• Marked as answer by Sunday, August 23, 2015 5:33 PM
Monday, August 10, 2015 9:47 AM
• In a calculated column.
```WeeksSinceLastStatus=
(MyTable[From_StatusDate]
- CALCULATE(
VALUES(MyTable[From_StatusDate])
,ALL(
MyTable
,MyTable[From_StatusDate]
,MyTable[From_StatusID]
,MyTable[To_StatusID]
)
,MyTable[To_StatusID] = EARLIER(MyTable[From_StatusID])
)
) / 14```

This would be better done in an ETL tool as mentioned above.

Here you're getting fractional weeks, which may or may not match what you want. There are a number of rounding functions you may prefer to wrap around the entire expression to get integer values.

If you are using the 2016 preview, then the DATEDIFF() function is what you want.

GNet Group BI Consultant

• Proposed as answer by Thursday, August 13, 2015 6:45 AM
• Marked as answer by Sunday, August 23, 2015 5:33 PM
Monday, August 10, 2015 1:15 PM

### All replies

• This one is pretty interesting. I would probably create two calculated fields that concatenated JobOrderId and FromStatusID and JobOrderId and To_StatusID respectively.  You could then probably use a combination of DAX filters to create a calculated field (measure) that calculated the amount of weeks or days between the From_StatusDate where your Calc1 equaled Cacl2.

http://www.daxpatterns.com/cumulative-total/

Too late to really sit down and try to think through this one but maybe some of these thoughts will get you started down the right path.

Monday, August 10, 2015 3:46 AM
• You could also consider this a job to be done before loading your data into Power Pivot, using it's ETL-tool Power Query. This would be your code then - just replace "YourTable" by the Name of your table & choose the "Load to Data Model"-Option:

let
Source = Excel.CurrentWorkbook(){[Name="YourTable"]}[Content],
SelfJoin = Table.NestedJoin(Source,{"JobOrderId", "To_StatusID", "CandidateId"},Source,{"JobOrderId", "From_StatusID", "CandidateId"},"NewColumn"),
ExpandStatusChangeDate = Table.ExpandTableColumn(SelfJoin, "NewColumn", {"From_StatusDate"}, {"From_StatusDate.1"}),
CalcWeeks = Table.AddColumn(ExpandStatusChangeDate, "Weeks", each ([From_StatusDate.1]-[From_StatusDate])/7),
ChangeType = Table.TransformColumnTypes(CalcWeeks,{{"Weeks", type number}})

in
ChangeType

Imke

• Proposed as answer by Thursday, August 13, 2015 6:45 AM
• Marked as answer by Sunday, August 23, 2015 5:33 PM
Monday, August 10, 2015 9:47 AM
• In a calculated column.
```WeeksSinceLastStatus=
(MyTable[From_StatusDate]
- CALCULATE(
VALUES(MyTable[From_StatusDate])
,ALL(
MyTable
,MyTable[From_StatusDate]
,MyTable[From_StatusID]
,MyTable[To_StatusID]
)
,MyTable[To_StatusID] = EARLIER(MyTable[From_StatusID])
)
) / 14```

This would be better done in an ETL tool as mentioned above.

Here you're getting fractional weeks, which may or may not match what you want. There are a number of rounding functions you may prefer to wrap around the entire expression to get integer values.

If you are using the 2016 preview, then the DATEDIFF() function is what you want.

GNet Group BI Consultant

• Proposed as answer by Thursday, August 13, 2015 6:45 AM
• Marked as answer by Sunday, August 23, 2015 5:33 PM
Monday, August 10, 2015 1:15 PM