Forum Discussion

clock0928's avatar
clock0928
Icon for Helper I rankHelper I
2 years ago
Solved

WeekOfYear showing data from multiple years

Hi all,

 

I have a calendar table and in the calendar table I have a "WeekOfYear" column.

When I use the WeekOfYear column in a table visual, the resulting data alongside WeekOfYear is showing for the same week of multiple years.

 

For example, currently we are in week 8 of 2024, within my table it is also showing data from week 8 of 2023, 2022, 2021, 2020. How can I make it only show data for WeekOfYear for the current year?

 

 

Currently I have a slicer that has "Year" and "WeekOfYear", and my calendar table is linked to the appropriate tables.

 

Any suggestions would be really helpful! 😊

Need Help
  • clock0928's avatar
    clock0928
    2 years ago

    lbendlin - I thought instead of leaving your comment with no response, I would advise that I did not have time to supply you with the data - my apologies again if that offended you.
    However, I did work out the solution with the asssist from Copilot.

    For future reference and if anyone else gets this form of error within their own report - 
    All I had to do was drop

    ALL('Calendar')

     from my measure. As this was resulting in the filter to view the entire dataset across all dates, and not just the ones currently being looked at.

7 Replies

  • clock0928's avatar
    clock0928
    Icon for Helper I rankHelper I
    2 years ago

    Adding to the above...
    It might also have something to do with my measure for "Total Work Completed" - would I be correct in thinking that?
    This is the measure - 

    msrTotalWorkCompleted = 
CALCULATE(
    SUM('Daily Statistics'[Tasks 4022]),
        'Calendar'[WeekOfYear] = SELECTEDVALUE('Calendar'[WeekOfYear]) - 0, ALL('Calendar')
    )
    • lbendlin's avatar
      lbendlin
      Icon for Super User rankSuper User
      2 years ago

      First of all you will have to define what you mean by "current year".  If you want this to be deterministic then you have to add the year to your visual, ideally in the same area as the week, just above it in the hierarchy. Or you have to add a Year_Week column to your calendar table and use that instead.

      • clock0928's avatar
        clock0928
        Icon for Helper I rankHelper I
        2 years ago

        Thanks for the response lbendlin - Current year in my view would be the year selected in my slicer...

         

        So I have tried adding in the year to my table visual and still receive the error. Then I have tried with the new "YearWeek" column -

         

        YearWeek = FORMAT('Calendar'[Year], "0000") & FORMAT('Calendar'[WeekOfYear], "00")

         

        However still the same error I am facing.