cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
JoãoBerryBR Frequent Visitor
Frequent Visitor

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 2019 BY calendar. to do that in Excel, I´ve make this formula: 

 

"=IF([@[Anticipated Completion Date]]>$E$2;0;IF([@[Anticipated Completion Date]]<=$E$1;IF(365-($E$1-[@[Anticipated Completion Date]])>=0;(365-($E$1-[@[Anticipated Completion Date]]));0);$E$2-[@[Anticipated Completion Date]]))"

Where:

  • Anticipated completion date = The project duedate, when is supposed to be finished;
  • E1 = 30/09/2018 (End of the 2018 BY calendar);
  • E2 = 30/09/2019 (End of the 2019 BY calendar);
  • 365 = number of days in a regular year;

Regards,

1 ACCEPTED SOLUTION

Accepted Solutions
Community Support Team
Community Support Team

Re: Project completion date VS BY calendar - Projects impacts

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))

1.png

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 )
        )
    )

3.png

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.

7 REPLIES 7
Stachu Super Contributor
Super Contributor

Re: Project completion date VS BY calendar - Projects impacts

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 

Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

Community Support Team
Community Support Team

Re: Project completion date VS BY calendar - Projects impacts

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))

1.png

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 )
        )
    )

3.png

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 Frequent Visitor
Frequent Visitor

Re: Project completion date VS BY calendar - Projects impacts

Thank you, I will try it.

 

Kind regards,

JoãoBerryBR Frequent Visitor
Frequent Visitor

Re: Project completion date VS BY calendar - Projects impacts

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.

JoãoBerryBR Frequent Visitor
Frequent Visitor

Re: Project completion date VS BY calendar - Projects impacts

hi,

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

"modified year"

 Regards,

Community Support Team
Community Support Team

Re: Project completion date VS BY calendar - Projects impacts

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 Frequent Visitor
Frequent Visitor

Re: Project completion date VS BY calendar - Projects impacts

Hi Maggie,

thanks a lot!!!

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

 

regards,

João

Helpful resources

Announcements
Community News & Announcements

Community News & Announcements

Get your latest community news and announcements.

Summit North America

Power Platform Summit North America

Register by September 5 to save $200

Virtual Launch Event

Microsoft Business Applications Virtual Launch Event

Watch the event on demand for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Users Online
Currently online: 381 members 3,435 guests
Please welcome our newest community members: