Forum Discussion

tonyclifton's avatar
tonyclifton
Icon for Helper III rankHelper III
7 years ago
Solved

Create start and end date columns based on milestone dates

Hello community,   I have the following table: | Project | Date | Milestone Name | Milestone ID | | ------- | ---------- | -------------- | ------------ | | A | 2019-02-08 | MS 1 ...
Need Help
  • Mariusz's avatar
    Mariusz
    7 years ago

    Hi tonyclifton 

    You can do it as Column (Please see the below) or Measure, but its better to do it in Query Editor.

    Start Date = 
VAR endDate = YourTable[End Date]
VAR startDate = CALCULATE(
    MAX( YourTable[End Date] ),
    ALLEXCEPT( YourTable, YourTable[Project] ),
    YourTable[End Date] < endDate
)
RETURN IF( ISBLANK( startDate ), DATE( YEAR( endDate ), 1, 1 ), startDate )

     

    Many Thanks

    Regards,
    Mariusz

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

tonyclifton's avatar
tonyclifton
Icon for Helper III rankHelper III
7 years ago

Mariusz  thanks alot this works for me. Any chance to see the same functionality in a DAX column/measure?

Mariusz's avatar
Mariusz
Icon for Community Champion rankCommunity Champion
7 years ago

Hi tonyclifton 

You can do it as Column (Please see the below) or Measure, but its better to do it in Query Editor.

Start Date = 
VAR endDate = YourTable[End Date]
VAR startDate = CALCULATE(
    MAX( YourTable[End Date] ),
    ALLEXCEPT( YourTable, YourTable[Project] ),
    YourTable[End Date] < endDate
)
RETURN IF( ISBLANK( startDate ), DATE( YEAR( endDate ), 1, 1 ), startDate )

 

Many Thanks

Regards,
Mariusz

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

  • tonyclifton's avatar
    tonyclifton
    Icon for Helper III rankHelper III
    7 years ago

    Perfect. Thank you very much.