how to count different records in every week in every year?

[noamshn]

hello everyone,

I have thie measure:

Completed In every year (Week) = CALCULATE(
COUNTROWS('Intelex'),
USERELATIONSHIP(WeekDates[WeekNum],'Intelex'[Week Completed]),
'Intelex'[Status]="Closed"
) + 0

the problem is that it counts the records from all the years for a single week. let's say i have week number 1, it counts the records from year 2022, 2023 and 2024 this week. I want it to count the records in week number 1 in 2022, week number 1 in 2023 and week number 1 in 2024.

what am i doing wrong? how can i solve it?

[Anonymous · Accepted solution]

Hi  @noamshn ,

I created some data:

Intelex：

WeekDates:

Here are the steps you can follow：

1. Create measure.

Measure =
var _select=SELECTEDVALUE('WeekDates'[WeekNum])
return
COUNTX(
    FILTER(ALL(Intelex),
    'Intelex'[Week Completed]=_select&&'Intelex'[Week Completed]=MAX('WeekDates'[WeekNum])&&'Intelex'[Status]="Closed"),[Date])

2. Result:

Best Regards,

Liu Yang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

[Anonymous]

As per what I see looking at this measure and your requirement. All you need now is a date table and a slicer with year column in it. I think you'll be able to filter out the based on that. 

Thankyou.