Forum Discussion

tisci456's avatar
tisci456
New Member
3 years ago
Solved

Counting rows after a certain date but capped at a certain number of weeks?

I have a table of years and months connected 1:* to a table of weeks. I'm trying to count the number of weeks within the year (either 52 or 53) but capped at the number of weeks a person has remaining in a year. For example, a person who starts on 12/1/21 should have the end result look something like this:

2021  5
 December 5
  12/3/215
  12/10/215
  12/17/215
  12/24/215
  12/31/215
2022  52
 January 52
  1/7/2252
  1/14/2252
  1/21/2252
  1/28/22

52

 

I have a slicer for year/month/week on this report as well but it shouldn't affect the numbers for month/week in this measure.

 

I started off with:

 

CALCULATE(
    COUNTROWS(Weeks),
    FILTER(
        ALL(Weeks),
        Weeks[Week Ending] >= MIN(Employee[Start Date])
    )
)

 

This DAX will give me the count of all weeks after the start date (i.e. 57) for every week/month/year I have in my year/month and week tables (even prior to the start date), which is where I'm now stuck. What should I add/change from here to get the measure to calculate how I want?

Need Help
  • Anonymous's avatar
    Anonymous
    3 years ago

    Hi tisci456 ,

     

    Here I suggest you to create a calendar table by CALENDAR() or CALENDARAUTO function to achieve your goal.

    Calendar =
ADDCOLUMNS (
    CALENDAR ( DATE ( 2021, 12, 01 ), DATE ( 2022, 12, 31 ) ),
    "Year", YEAR ( [Date] ),
    "Month", MONTH ( [Date] ),
    "MonthLongName", FORMAT ( [Date], "MMMM" ),
    "WeekName", WEEKNUM ( [Date], 2 ),
    "WeekDay", WEEKDAY ( [Date], 2 )
)

    Measure:

    Measure = 
VAR _SELECTSTARTDATE = SELECTEDVALUE(User[Start Date])
VAR _SELECTENDDATE = SELECTEDVALUE(User[End Date])
RETURN
CALCULATE(DISTINCTCOUNT('Calendar'[WeekName]),FILTER(ALLEXCEPT('Calendar','Calendar'[Year]),'Calendar'[Date]<=_SELECTENDDATE && 'Calendar'[Date]>=_SELECTSTARTDATE))

    Result is as below.

     

    Best Regards,
    Rico Zhou

     

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

     

     

3 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    Hi tisci456 ,

     

    Here I suggest you to create a calendar table by CALENDAR() or CALENDARAUTO function to achieve your goal.

    Calendar =
ADDCOLUMNS (
    CALENDAR ( DATE ( 2021, 12, 01 ), DATE ( 2022, 12, 31 ) ),
    "Year", YEAR ( [Date] ),
    "Month", MONTH ( [Date] ),
    "MonthLongName", FORMAT ( [Date], "MMMM" ),
    "WeekName", WEEKNUM ( [Date], 2 ),
    "WeekDay", WEEKDAY ( [Date], 2 )
)

    Measure:

    Measure = 
VAR _SELECTSTARTDATE = SELECTEDVALUE(User[Start Date])
VAR _SELECTENDDATE = SELECTEDVALUE(User[End Date])
RETURN
CALCULATE(DISTINCTCOUNT('Calendar'[WeekName]),FILTER(ALLEXCEPT('Calendar','Calendar'[Year]),'Calendar'[Date]<=_SELECTENDDATE && 'Calendar'[Date]>=_SELECTSTARTDATE))

    Result is as below.

     

    Best Regards,
    Rico Zhou

     

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

     

     

    • tisci456's avatar
      tisci456
      New Member
      3 years ago

      Is the calendar table supposed to be a disconnected table or can I use the year/month and week tables I already have?

      • Anonymous's avatar
        Anonymous
        Not applicable
        3 years ago

        Hi tisci456 ,

         

        I think your data table should contain [Start Date] and [End Date]. 

         

        According to my test, the relationship between date table and fact table will impact the measure and cause incorrect result.

        So Calendar table should be a disconnected table and you can add the filter in measure code to achieve your goal.

         

        Then if you already have year/month and week tables , please make sure there are continuous date in them, this will make your calculation easier.

         

        Best Regards,
        Rico Zhou

         

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