Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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
Solved! Go to 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
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
Can you provide some sample data and an explanation of "networking days"?
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.
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
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")))
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
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")))
User | Count |
---|---|
93 | |
84 | |
78 | |
75 | |
66 |
User | Count |
---|---|
115 | |
105 | |
93 | |
65 | |
60 |