locked
How can I do a nested date calculation with DAX? RRS feed

  • 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

Answers

  • 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 Michael Amadi Thursday, August 13, 2015 6:45 AM
    • Marked as answer by Michael Amadi Sunday, August 23, 2015 5:33 PM
    Monday, August 10, 2015 9:47 AM
    Answerer
  • 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 Michael Amadi Thursday, August 13, 2015 6:45 AM
    • Marked as answer by Michael Amadi 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.

    This aggregation pattern may help you in this:

    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 Michael Amadi Thursday, August 13, 2015 6:45 AM
    • Marked as answer by Michael Amadi Sunday, August 23, 2015 5:33 PM
    Monday, August 10, 2015 9:47 AM
    Answerer
  • 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 Michael Amadi Thursday, August 13, 2015 6:45 AM
    • Marked as answer by Michael Amadi Sunday, August 23, 2015 5:33 PM
    Monday, August 10, 2015 1:15 PM