Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago

Splitting Weeks and data

 

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

General Comment
Tips and Tricks

2 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

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

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      7 years ago

      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