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

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Reply
clock0928
Helper I
Helper I

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?

 

clock0928_0-1708477611652.png

 

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! 😊

1 ACCEPTED SOLUTION

@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.

View solution in original post

7 REPLIES 7
clock0928
Helper I
Helper I

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')
    )

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.

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.

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).

Do not include sensitive information or anything not related to the issue or question.

If you are unsure how to upload data please refer to https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...

Please show the expected outcome based on the sample data you provided.

Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...

@lbendlin - Unfortunately I don't have time available to put together sample data. Appreciate your time though

What a remarkable reply. (Even a little ironic given your username). Do I have your permission to use this as an anonymized quote?

@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.

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

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

April Fabric Community Update

Fabric Community Update - April 2024

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