Forum Discussion

JoãoBerryBR's avatar
JoãoBerryBR
New Member
7 years ago
Solved

Project completion date VS BY calendar - Projects impacts

Hi all, How to implement if with powerquery codes?   I´d like to make a "date" formate data calculation, the proposal is to measure the impact of some projects, based on the Completion Date vs the...
Need Help
Show and Tell
Tutorial Requests
  • v-juanli-msft's avatar
    v-juanli-msft
    7 years ago

    Hi JoãoBerryBR 

    Create a calendar table without creating any relationship

    calendar = ADDCOLUMNS(CALENDARAUTO(),"modified year",IF(MONTH([Date])<10,YEAR([Date]),YEAR([Date])+1))

    Create measures in main data table

    measure =
VAR end_2018 =
    CALCULATE (
        MAX ( 'calendar'[Date] ),
        FILTER ( ALL ( 'calendar' ), 'calendar'[modified year] = 2018 )
    )
VAR end_2019 =
    CALCULATE (
        MAX ( 'calendar'[Date] ),
        FILTER ( ALL ( 'calendar' ), 'calendar'[modified year] = 2019 )
    )
RETURN
    IF (
        MAX ( 'Table'[Anticipated Completion Date] ) > end_2019,
        0,
        IF (
            MAX ( 'Table'[Anticipated Completion Date] ) <= end_2018,
            IF (
                365 - DATEDIFF ( MAX ( 'Table'[Anticipated Completion Date] ), end_2018, DAY ) >= 0,
                365 - DATEDIFF ( MAX ( 'Table'[Anticipated Completion Date] ), end_2018, DAY ),
                0
            ),
            DATEDIFF ( MAX ( 'Table'[Anticipated Completion Date] ), end_2019, DAY )
        )
    )

    Best Regards
    Maggie

     

    Community Support Team _ Maggie Li
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

JoãoBerryBR's avatar
JoãoBerryBR
New Member
7 years ago

Hi,

  • the first IF is to exclude the Aticipated complention date before the current BU year, in this case, 30/09/2019, if it´s bigger then 30/09/2019, the result will be Zero;
  • the second IF will test how many days from the previouls year, in this case, from 01/10/2017 until 30/09/2018, will impact the current BU, in this case 2019. The calculation consider 12 month of projects benefits, in this example, if one project was finished in 30/03/2018, the project benefit calculation will consider 6 months in the BU 2018 and 6 months in the BU2019;
  • The second if also, avoid negative date, for example: one project was finished in 30/06/2016, the calculation result will be:  -457, which does not make sense, so the IF will fill the field with Zero.

regards.