Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
JoãoBerryBR
New Member

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
v-juanli-msft
Community Support
Community Support

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.

View solution in original post

7 REPLIES 7
JoãoBerryBR
New Member

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.

v-juanli-msft
Community Support
Community Support

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.

Thank you, I will try it.

 

Kind regards,

Stachu
Community Champion
Community Champion

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!
Thank you for the kudos 🙂

hi,

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

"modified year"

 Regards,

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.

Hi Maggie,

thanks a lot!!!

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

 

regards,

João

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.

Top Solution Authors
Top Kudoed Authors