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
bvdm1980
Helper I
Helper I

show number of months (duration) instead of calender months

Hi, i have a lot of projects that run over 1 or more years. And to make comparison in what period of a project duration costs are made, i want to have the period visible of a projects duration.

I searched lot of stuff on internet, and found some answers with datediff dax function, but couldnt solve it .

So lets say i have 2 projects. One starts in jan 2022 and ends in june 2023. Its now december 2022 so jan is month 1 of the project, and today is month 12 of the project duration.

2nd project starts in june 2022 and ends in feb 2023, so start date is month/period 1 , december is month 7 etc.

How do i get this information in my tables? Do i need to add a column in query editor with some dax? Or do i need to create a new measure? 

I already added a measure to determine the duration of a project in months with

"duration = (today() - projecttable[startdate]) / 30"

which work as it shows currently 14th month for project started in june 2021. 

3 REPLIES 3
v-cgao-msft
Community Support
Community Support

Hi @bvdm1980 ,

 

If the start date takes up a month and you want a measure, please try this:

duration = 
DATEDIFF(
    MAX(projecttable[startdate]),
    TODAY(),
    MONTH
)+1

vcgaomsft_0-1671522382515.png

Best Regards,
Gao

Community Support Team

 

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly --  How to provide sample data in the Power BI Forum

thanks, i got this already except the fact that it still doesnt give me a bar chart with costs per project month

i get 1 bar, with 1 amount for total project. So somehow it doesn't give me each specific project month as a data field. 

So i would want, for every project i select, a bar chart showing the costs per period since start date of the selected project. 

This allows me to compare every n-th period of a project costwise. Which could (f.e.) show that most costs are made in the 4th till 6th month after a project started. 

Hope this is clearer 🙂

Manoj_Nair
Solution Supplier
Solution Supplier

@bvdm1980 , your DAX is correct. Add it as a column in the front end. Something like this. Let me know if this ok

image.jpg

If my post helps you to find solution would be happy if you could mark my post as a solution and give it a thumbs up

Best regards

Manoj Nair

Linkedin - https://www.linkedin.com/in/manoj-nair-%E2%98%81-344666104/

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