Countrows during a range of date

[Anonymous]

Hi all,

I am trying to create a PAST DUE Kpi for trainings. Where it will return the 1 for employee who has Past due date set and didnt have complete, however I'd like during the time (after the past due date and before completion date) when i use my slicer with calendar shows the name of employee.

Example:

                                  Registration             Training tile       Past due Date          completion date 

Employee1                09/09/2019                Marketing         01/10/2019                   03/01/2020

Employee2                15/09/2019             communication    15/10/2019                    

Employee3                09/09/2019                Marketing          01/10/2019                  01/12/2019

What i am trying to achive is : 

OCT 3  NOV 2 DEC 1 JAN 0 

I have create this measeure, which is returning the corect value for the correct for oct, but when i select nov is comes blank, and as the  past due date is before the calendar date and completion date is blank or before the calendar date, it show count them, however it's not: 

TRA - Past Due = CALCULATE (
            COUNTROWS (
            FILTER ( TRAINING,
                    TRAINING[Past due date] <= MAX ( Calendar[Date] )
                    && (ISBLANK ( TRAINING[Completed Date] ) || TRAINING[Completed Date] >= MAX ( Calendar[Date] ) )
                   )),USERELATIONSHIP(TRAINING[Past due date],'CALENDAR'[Date])) 

I am using Userralationhip, as this table has another relationship active.

[v-jayw-msft]

Hi @Anonymous ,

Please remove the USERELATIONSHIP() function in your formula and create a calculated column as slicer.

YearMonth = FORMAT('CALENDAR'[Date],"YYYYMM")

 BTW, I think there's something wrong with your logic. Based on your logic the value should be OCT 3  NOV 3 DEC 2 JAN 1.

Best Regards,

Jay

Community Support Team _ Jay Wang

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

[Anonymous]

Hi @v-jayw-msft , 

Thanks for your reply. 

I have as slicer a calendar which is a separate table. 

There is active trelationship from Registration date to Calendar date. 

If I dont put the userrelationship it wont give me the results related to Past due date. 

Related to the logic, Jan should be one if you acess the dashboard until 03/01/2020 after this date it should shows as 0, as the trainings has been completed. 

[v-jayw-msft]

Hi @Anonymous ,

Then you will need ALL() function to remove the filter that was brought by Calendar table.

FILTER ( ALL(TRAINING),

Best Regards,

Jay

Community Support Team _ Jay Wang

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

[Anonymous]

Hi @v-jayw-msft ,

I have amend the DAX As: 

CALCULATE (
            COUNTROWS (
            FILTER ( ALL(TRAINING),
                    TRAINING[Past due date] <= MAX ( Calendar[Date] )
                    && (ISBLANK ( TRAINING[Completed Date] ) || TRAINING[Completed Date] >= MAX ( Calendar[Date] ) )
                   )),USERELATIONSHIP(TRAINING[Past due date],'CALENDAR'[Date]))

And 

CALCULATE (
            COUNTROWS (
            FILTER ( ALL(TRAINING),
                    TRAINING[Past due date] <= MAX ( Calendar[Date] )
                    && (ISBLANK ( TRAINING[Completed Date] ) || TRAINING[Completed Date] >= MAX ( Calendar[Date] ) )
                   )))

Both of them is not bringing the correct figures

[Anonymous]

Hi @v-jayw-msft 

As you can see below, I have several interfaces with TRAINING table, (1 active and 2 inactive), that's why I was using userrelashionship to get the information related to the relation to Past due date. 

By now the measue with the DAX as: 

TRA - Past Due = CALCULATE (
            COUNTROWS (
            FILTER ( TRAINING,
                    TRAINING[Past due date] <= MAX ( Calendar[Date] )
                    && (ISBLANK ( TRAINING[Completed Date] ) || TRAINING[Completed Date] >= MAX ( Calendar[Date] ) )
                   )),USERELATIONSHIP(TRAINING[Past due date],'CALENDAR'[Date]))

returns me the correct figures for the month when I select a Calendar month on the slicer and if the Past due month is the same as the calendar date , example Oct 2019: 

However when I select the following month example NOV, I was expecting to get the same figures as OCT (as the Past due due is before than selected calendar date and completion date is balnk or after the selected calendar date) + Any PAst due trainings starting in November, however it shows only the ones with Start date in November: 

Adding ALL as your advice it returns the wrong figures for the OCT for example: 

[v-jayw-msft]

Hi @Anonymous ,

Here's the thing, when you use USERELATIONSHIP() function to active the relationship between TRAINING[Past due date] and CALENDAR[Date], it will filter out the data which TRAINING[Past due date] is equal to the month you selected. So, in your scenario, when you select Nov, none of the TRAINING[Past due date] is in Nov and the result would be blank. When use ALL() function, it will remove the filter which was caused by your silcer then execute the conditions.

Here's Pbix I made, hope it will help you understand.

Best Regards,

Jay

Community Support Team _ Jay Wang

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

[Anonymous · Accepted solution]

hi @v-jayw-msft, 

Thanks for your reply, however your suggestion was isn't worked as it was not showing the quantity. But dived a bit more i found a solution. 

The issue was due to relation, to have the result as I was experting example : OCT 3  NOV 3 DEC 2 JAN 1 , NO relation should be active. So i have inactive all relation between the Trainings table and Calendar and I have created tricked the measure for : 

Past_Due = 
VAR currentDate = MAX('CALENDAR'[Date])
RETURN
CALCULATE (
COUNTROWS('TRAINING'),
AND( NOT(ISBLANK('TRAINING'[Past due date])),'TRAINING'[Past due date]<= currentDate),
'TRAINING'[Completed Date] >= currentDate 
)