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.

Stachu's avatar
Stachu
Community Champion
7 years ago

I don't get the formula, it seems the true in second IF can never happen (date>E2 (2019), and date <=E1 (2018))
can you describe it natural language?

in M it can only be return as a column or a table, and it seems more like a measure for DAX - counting days from a given date to the end of a custom calendar year. If you do need it in a column, can you add sample tables (in format that can be copied to PowerBI) from your model with anonymised data?

Column1 Column2
A 1
B 2.5

As described here:

How to Get Your Question Answered Quickly 

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

hi,

Sorry for my dumb question but what parameter I need to put in the field below?

"modified year"

 Regards,

  • v-juanli-msft's avatar
    v-juanli-msft
    Community Support
    7 years ago

    Hi JoãoBerryBR 

    It is a column added in "calendar" table,it is written in my first formula.

    The start of a year and end of a year is from 9/30 this year to 6/30 next year, eg, 2018/9/30~2019/6/30, it represents for year 2019, right?

     

    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 Maggie,

      thanks a lot!!!

      My 2019 BU year is from 2018/10/01 until 2019/09/30.

       

      regards,

      João