Need help with DAX to calculate total number of weeks between two dates

[Anonymous]

I have a started on date and ended on date (some are blank because they are still active) and I need to count the number of week between start date and end date. Thanks for the help! 

[selimovd]

Hey @Anonymous ,

you can do that with the DATEDIFF function:

Weeks between Start and End = DATEDIFF( MIN( myDateTable[StartDate] ), MAX( myDateTable[EndDate] ), WEEK)

If you need any help please let me know.

If I answered your question I would be happy if you could mark my post as a solution ✔️ and give it a thumbs up 👍

Best regards

Denis

Blog: WhatTheFact.bi

Follow me: twitter.com/DenSelimovic

[Anonymous]

@selimovd so that looks like it counts them all the same, members who are still active and members who have end dates. See my image. 

[selimovd · Accepted solution]

Hey @Anonymous ,

if you use it as calculated column you should not use the MIN and MAX functions.

Try it without:

Age in Weeks = DATEDIFF( fact_member[started_on], fact_member[ended_on] , WEEK )

By the way, what should happen if there is no end date? I think this we have to handle somehow.

If you need any help please let me know.

If I answered your question I would be happy if you could mark my post as a solution ✔️ and give it a thumbs up 👍

Best regards

Denis

Blog: WhatTheFact.bi

Follow me: twitter.com/DenSelimovic