How to create a calculated field by date interval

[mdrammeh]

I need help creating a calculated field in Power BI or Power Query for the following measures:

Projects Submitted 27 months prior to “Constructions start” date”

Projects Released 21 months prior to “Construction Year”( Construction Year is 3 years from today's date)

Projects Scheduled between 12-18 months prior to “Construction Year”

Scheduled 1 Year Prior to “Construction Start”

Assuming I have the above field names to complete my calculations. Keep in mind that I want o use networking days only  if possible. 

Thanks for all your help in advance. 

Mustaff

[BetterCallFrank · Accepted solution]

You can do sth very similar in DAX with DATEADD, e.g.

DATEADD( TheData[Date], -27, MONTH )

- however your overall scenario is not very clear (to me) - do you e.g. want to count projects?

Frank

[Greg_Deckler]

Can you provide some sample data and an explanation of "networking days"?

[mdrammeh]

In Excel it would translate to something like this:

=DATE(YEAR(B33),MONTH(B33)+27,DAY(B33))

=DATE(YEAR(B33),MONTH(B33)-27,DAY(B33))

I tried to copy and paste the example but it didn't work. I hope this helps.

[Baskar · Accepted solution]

Ya this formula should work in Power BI without any change dude.

lets see my example .

But u have to create  calculated column in table . see the picture 

let me know if it is not helping u , i will help u , to reach your goal

[mdrammeh]

Thanks! The formula works but what if I want to create a lookup by duration from each date within the same column? Let's say I have a columns A,B, and C as my example where:

Column "A" = Start date

Column "B" = Finish Date

Column "C" = Duration

1. How can I calculate the duration by month between "A" and "B" to produce the count of month for each project. 

2. After Calculating the Duration, how can I use this Excel formula to group by days:

E.G.

=IF([@[Duration]]<1,"Less than 1 day",IF(AND([@[Duration]]>0,[@[Duration]]<56),"Between 1 and 55 days",IF(AND([@[Duration]]>55,[@[Duration]]<366),"Between 56 and 365 days","Greater than 365 days")))

[BetterCallFrank · Accepted solution]

You can do sth very similar in DAX with DATEADD, e.g.

DATEADD( TheData[Date], -27, MONTH )

- however your overall scenario is not very clear (to me) - do you e.g. want to count projects?

Frank

[mdrammeh]

Thanks! The formula works but what if I want to create a lookup by duration from each date within the same column? Let's say I have a columns A,B, and C as my example where:

Column "A" = Start date

Column "B" = Finish Date

Column "C" = Duration

1. How can I calculate the duration by month between "A" and "B" to produce the count of month for each project. 

2. After Calculating the Duration, how can I use this Excel formula to group by days:

E.G.

=IF([@[Duration]]<1,"Less than 1 day",IF(AND([@[Duration]]>0,[@[Duration]]<56),"Between 1 and 55 days",IF(AND([@[Duration]]>55,[@[Duration]]<366),"Between 56 and 365 days","Greater than 365 days")))