Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreShape the future of the Fabric Community! Your insights matter. That’s why we created a quick survey to learn about your experience finding answers to technical questions. Take survey.
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.
Kindly help to calculate the number of weeks using DAX
Solved! Go to Solution.
Hi @suma2413 ,
Here is my test data and Steps you can try:
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
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
Hi @suma2413 ,
Here is my test data and Steps you can try:
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
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.
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/
Your insights matter. That’s why we created a quick survey to learn about your experience finding answers to technical questions.
Check out the November 2024 Power BI update to learn about new features.
User | Count |
---|---|
21 | |
21 | |
21 | |
13 | |
12 |
User | Count |
---|---|
43 | |
28 | |
25 | |
23 | |
22 |