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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors