Forum Discussion

Displaced_2000's avatar
Displaced_2000
Frequent Visitor
2 years ago
Solved

DAX code to countrows for all cases active during a week

Hi,

 

I am wanting to show my data by each week and count the cases that are active in that specific week based upong start and end dates.  If the end date is blank it should be included in all weeks from start date to current date etc.

 

The tracker has a relationship to the date table (created) but as you can see im removing that link with CROSSFILTER but I still dont get the correct solution.

 

Is there anything obvious im missing here?

 

 

countWeeklyActive_V2 = 
VAR minDate = MIN(Dates[Date])
VAR maxDate = MAX(Dates[Date])
VAR countWeeklyActive1 =
        CALCULATE(
            COUNTROWS('tracker'),
            CROSSFILTER(Dates[Date],'tracker'[Created],None),
                FILTER('tracker',
                    NOT(ISBLANK('tracker'[ConditionalWeeklyActiveDate])) &&
                    'tracker'[Created] <= maxDate &&
                    'tracker'[ConditionalWeeklyActiveDate] > maxDate)
                    
                   
                )
                
VAR countWeeklyActive2 =
        CALCULATE(
            COUNTROWS('tracker'),
            CROSSFILTER(Dates[Date],'tracker'[Created],None),
                FILTER('tracker',
                    ISBLANK('tracker'[ConditionalWeeklyActiveDate]) &&
                    'tracker'[Created] <= maxDate
                )  
                )          

VAR BOTH = countWeeklyActive1 + countWeeklyActive2
RETURN
Both

 

 

 

 

Need Help
  • Anonymous's avatar
    Anonymous
    2 years ago

    Hi,Displaced_2000.Hello, hackcrr,thank you very much for your help on this issue, I would like to share my solution below.
    I am glad to help you.

    According to your description, you want to count the number of activities per week and count it as the current week if the end date is empty (the activity is included in all weeks from the start date to the current date)
    I have performed the following test as I understand it and you can refer to it:
    Since there is a relationship between the 'tracker' table and the dates table, I used the weekNum function to calculate the number of weeks for the start date and end date of each activity (counting the weeks in the current year), and the number of weeks to see how many weeks the activity spanned, then I counted the number of weeks for all the dates in the dates column, and finally created a measure,if the dates column in the dates is between the If the date column in dates is between the start and end weeks in the tracker table, then count the data in the 'tracker' table that meets this condition

    Here is the test data and Dax code

    The code of the calculate column 

    C_endTime = IF(ISBLANK('tracker'[ConditionalWeeklyActiveDate]),TODAY(),'tracker'[ConditionalWeeklyActiveDate]) 
    C_endWeek = WEEKNUM('tracker'[C_endTime],2)
    C_startWeek = WEEKNUM('tracker'[Created],2) 
    date_weekNum = WEEKNUM('Dates'[Date],2)

    The code of the result

    M_result = CALCULATE(
    COUNTROWS('tracker'),FILTER(ALL(tracker),SELECTEDVALUE(Dates[date_weekNum])<='tracker'[C_endTime]&&SELECTEDVALUE(Dates[date_weekNum])>='tracker'[C_startWeek])
)

    The result is shows below.

    I hope my code provides you with ideas to solve the problem, and you can optimize the code according to your own needs!

    If you have any more questions, please clarify in a follow-up reply.

    Best Regards,

    Carson Jian,

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

     

3 Replies

  • hackcrr's avatar
    hackcrr
    Memorable Member
    2 years ago

    Hi, Displaced_2000 

    You might want to try the following DAX expression:

    countWeeklyActive_V2 =   
VAR CurrentWeekStartDate = STARTOFWEEK(TODAY(), 2)
VAR CurrentWeekEndDate = ENDOFWEEK(TODAY(), 2)  
VAR countWeeklyActive1 =  
    CALCULATE(  
        COUNTROWS('tracker'),  
        'tracker'[Created] <= CurrentWeekEndDate,  
        'tracker'[ConditionalWeeklyActiveDate] >= CurrentWeekStartDate || ISBLANK('tracker'[ConditionalWeeklyActiveDate])  
    )  
RETURN  
    countWeeklyActive1

     

    Best Regards,

    hackcrr

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

    • Displaced_2000's avatar
      Displaced_2000
      Frequent Visitor
      2 years ago

      No thats not working for me, I dnt have the function STARTOFWEEK or ENDOFWEEK

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hi,Displaced_2000.Hello, hackcrr,thank you very much for your help on this issue, I would like to share my solution below.
    I am glad to help you.

    According to your description, you want to count the number of activities per week and count it as the current week if the end date is empty (the activity is included in all weeks from the start date to the current date)
    I have performed the following test as I understand it and you can refer to it:
    Since there is a relationship between the 'tracker' table and the dates table, I used the weekNum function to calculate the number of weeks for the start date and end date of each activity (counting the weeks in the current year), and the number of weeks to see how many weeks the activity spanned, then I counted the number of weeks for all the dates in the dates column, and finally created a measure,if the dates column in the dates is between the If the date column in dates is between the start and end weeks in the tracker table, then count the data in the 'tracker' table that meets this condition

    Here is the test data and Dax code

    The code of the calculate column 

    C_endTime = IF(ISBLANK('tracker'[ConditionalWeeklyActiveDate]),TODAY(),'tracker'[ConditionalWeeklyActiveDate]) 
    C_endWeek = WEEKNUM('tracker'[C_endTime],2)
    C_startWeek = WEEKNUM('tracker'[Created],2) 
    date_weekNum = WEEKNUM('Dates'[Date],2)

    The code of the result

    M_result = CALCULATE(
    COUNTROWS('tracker'),FILTER(ALL(tracker),SELECTEDVALUE(Dates[date_weekNum])<='tracker'[C_endTime]&&SELECTEDVALUE(Dates[date_weekNum])>='tracker'[C_startWeek])
)

    The result is shows below.

    I hope my code provides you with ideas to solve the problem, and you can optimize the code according to your own needs!

    If you have any more questions, please clarify in a follow-up reply.

    Best Regards,

    Carson Jian,

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