Splitting Weeks and data

Anonymous · ‎06-07-2019

I have a query with about 10 columns of descriptors and a few thousand rows of data. The excel file I am using is for forecasting and lists dates in the format "Jan19 to Jan19" etc. The edits I made allowed me to get a day, month, day number, and year format as seen above. I converted this to a week number. However, the week numbers are listed as 1, 5, 9, 14, 18, 22, 27, 31, 36, 40, 44, 49.

I need them as 1, 2, . . . , 51, 52. But while doing that I also need to get an average of the forecasted value for each week/week group.

Any help would be greatly appreciated. Ask questions for more clearity if this made no sense

Anonymous · ‎06-07-2019

using the DAX WeekNum(date) -- the weeks are right (starting on Sunday) .. not sure how you converted them

Anonymous · ‎06-10-2019

Sorry, I should've mentioned that the only dates are the first of every month. The only weeks that formula gives me are 1, 5, 9, . . . etc.

So I need to somehow get continuos weeks (1-52) and divide the values by 4 or 5 (depending on the month) to get the averages for each week in the respective months