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

Get certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now

Reply
suma2413
Frequent Visitor

Determine the number of weeks based on teh months/quarter selected *Date tabel has only Month data(

Hello,

I have a date table which only has Month level data ( year, quarter, Month). Based on the filter, need to calculate the number of weeks.

Single year will be chosen so, can start the week count from start of year. User can choose month from different quarters also. Or can choose a full quarter. If current month is also chosen, only completed week should be considered-Basically upto previous week. 

suma2413_0-1715848974059.png

Kindly help to calculate the number of weeks using DAX

1 ACCEPTED SOLUTION
v-heq-msft
Community Support
Community Support

Hi @suma2413 ,
Here is my test data and Steps you can try:

vheqmsft_0-1715939058360.png

Create measures:

startDateOfMonth = STARTOFMONTH('Table'[Year-Month])
endDateOfMonth = EOMONTH([startDateOfMonth],0)
Sum = 
IF(
    HASONEFILTER('Table'[Year-Month]),
    DATEDIFF([startDateOfMonth],[endDateOfMonth],WEEK),
    SUMX('Table',DATEDIFF([startDateOfMonth],[endDateOfMonth],WEEK))
)

Final output

vheqmsft_1-1715939169928.png

Best regards,
Albert He

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

3 REPLIES 3
v-heq-msft
Community Support
Community Support

Hi @suma2413 ,
Here is my test data and Steps you can try:

vheqmsft_0-1715939058360.png

Create measures:

startDateOfMonth = STARTOFMONTH('Table'[Year-Month])
endDateOfMonth = EOMONTH([startDateOfMonth],0)
Sum = 
IF(
    HASONEFILTER('Table'[Year-Month]),
    DATEDIFF([startDateOfMonth],[endDateOfMonth],WEEK),
    SUMX('Table',DATEDIFF([startDateOfMonth],[endDateOfMonth],WEEK))
)

Final output

vheqmsft_1-1715939169928.png

Best regards,
Albert He

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

 

 



Thsis helps, But still doesnt provide week count which are complete..Hence I introduced WeekNo and then wrote DAX to calculate only complete weeks.

mark_endicott
Super User
Super User

I would suggest using a proper date table, this will make your life a whole lot easier. 

 

There are some great examples of extended date tables out there that can be created easily in Power Query. 

 

Here is a good article with some steps and a link to the code you can copy and paste. 

 

https://blog.enterprisedna.co/using-the-m-function-to-create-an-extended-power-bi-date-table/

Helpful resources

Announcements
November Carousel

Fabric Community Update - November 2024

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

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.

Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

Check out the November 2024 Power BI update to learn about new features.