Counting Rows By Date Column Where The Date Is Between A Specific Period

[FlyKick]

Hi All,

I have two tables one holds all my data and the other is a Date table that I have created in M Query. 

Tickets Table 

Ticket Number, Entered DateTime, Closed DateTime, EnteredDateOnly(Date only from Entered DateTIme), ClosedDateOnly(Date only)

Date Table (formed by taking distinct union of EnteredDateOnly and ClosedDateOnly)

*With exception of the date all other columns are created in M query 

Date

DayOfWeekNo

DayOfWeekLabel

WeekNo

MonthNo

MonthlLabel

Year

I am trying to create a measure to count the number of closed tickets within a time period and have tried the following.

• TotalClosed = CALCULATE(COUNTROWS('Tickets'),DATESBETWEEN('Tickets'[ClosedDateOnly],MIN('Date'[Date]),MAX('Date'[Date])))
• TotalClosed = CALCULATE(COUNTROWS('Tickets'),DATESBETWEEN('Tickets'[ClosedDateOnly],FIRSTDATE('Date'[Date]),LASTDATE('Date'[Date])))
• TotalClosed = CALCULATE(COUNTROWS('Tickets'), FILTER('Tickets','Tickets'[ClosedOnDateOnly] >= MIN('Date'[Date]) && 'Tickets'[ClosedOnDateOnly] <= MAX('Date'[Date])))

On my report I have a slicer based on the Date column from the Date table. The Date table has a one to many relationship with the Tickets table on the column EnteredDateOnly.

Both measures above work and provide the correct result when the slicer is at the min and max of the tickets table. However, the second I use the slicer to filter for any time period it displays the wrong count. I have validated by adding a Card visual and filtering manually on the ClosedDateOnly column using the range in the slicer. 

I have validated that MAX/MIN/FIRSTDATE/LASTDATE('Date'[Date]) returns the correct dates by creating measures for them all and using Card visuals. So I know the dates being passed to the functions are correct. I have also validated that the functions are ignoring blanks. 

I know the logic of the functions seems to be correct also and that it works when the Date isn't filtered. I have checked my relationship and tried the Cross Filter Direction Both and Single. 

I can't for the life of me figure out how Power BI is counting the records such that their is a wide discrepancy, especially when it works when the slicer is at the min and max date range of the Tickets data table. 

What am I missing here? It seems like it should work???? 

[v-zhenbw-msft]

Hi @FlyKick ,

Maybe the relationship affected the result.

Please delete the relationship between Tickets table and date table, then use the following measure.

TotalClosed = CALCULATE(COUNTROWS('Tickets'), FILTER('Tickets','Tickets'[ClosedOnDateOnly] >= MIN('Date'[Date]) && 'Tickets'[ClosedOnDateOnly] <= MAX('Date'[Date])))

If it doesn’t meet your requirement, could you please provide a mockup sample based on fake data?

It will be helpful if you can show us the exact expected result based on the tables.

Please upload your files to OneDrive For Business and share the link here. Please don't contain any Confidential Information or Real data in your reply.

Best regards,

Community Support Team _ zhenbw

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

BTW, pbix as attached.

[FlyKick]

Hi Everyone,

So after a good sleep and clear head I have figured out why I am not getting the results I expect. For everyones benefit I did create a continious date table as suggested but that didn't fix my problem which I expected as the filtered date range I was testing with did in fact already contain continious dates.

The issue is I am wanting to find all the tickets that were entered or closed between a specific date period. I am filtering the table by entered date and then counting the rows that have a closed date. But this ignores tickets that were entered prior to that time period. 

So I have fixed the measure by using the ALLEXCEPT function to remove the EnteredDate filter from the Tickets table. I tried to use the All(Column) function but I couldn't get that to work. It kept saying it was returning a table but exepcting a number. 

Here is the updated expression

Closed On-TotalTickets = CALCULATE(COUNTROWS('Tickets'), FILTER(ALLEXCEPT('Tickets','Tickets'[Board]),'Tickets '[ClosedOnDateOnly] >= MIN('Date'[Date]) && 'Tickets'[ClosedOnDateOnly] <= MAX('Date'[Date])))

However, the problem with this is my graph now shows the entire range of dates, I am assuming becasue the ALL function is removing the date filter. I am confused as to how the ALLEXCEPT function works. I thought it would only apply to the measure and return the results not also apply to the graph. 

Have I miss understood how it works? Any suggestions on how to resolve this issue? Thanks

[FlyKick]

Here is the result of adding the ALLEXCEPT function.

[v-zhenbw-msft]

Hi @FlyKick ,

How about the result after you follow the suggestions mentioned in my original post?

Could you please provide more details or expected result about it If it doesn't meet your requirement?

If you've fixed the issue on your own please kindly share your solution. If the above posts help, please kindly mark it as a solution to help others find it more quickly.

Best regards,

Community Support Team _ zhenbw

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

[v-zhenbw-msft · Accepted solution]

Hi @FlyKick ,

If your X axis uses Tickets table's Date, you can create a measure and put it in Filter on this visual. Configure it as 1.

Measure = 
IF(
    MAX(Tickets[ClosedOnDateOnly])>=MIN('Date'[Date]) && MAX(Tickets[ClosedOnDateOnly])<=MAX('Date'[Date]),1,0)

If your X axis uses Date table's Date, please try to delete the relationship between the date table and Tickets table.

If it doesn’t meet your requirement, could you please provide a mockup sample based on fake data?

It can help us reproduce your issue.

Please upload your files to OneDrive For Business and share the link here. Please don't contain any Confidential Information or Real data in your reply.

Best regards,

Community Support Team _ zhenbw

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

[Fowmy]

@FlyKick 

Can you change the relationship to [ClosedDateOnly and try.
If this doesn't work,  you can attach your PBIX file with sample data, I will be able to check it.

________________________

Did I answer your question? Mark this post as a solution, this will help others!.

Click on the Thumbs-Up icon on the right if you like this reply 🙂

YouTube, LinkedIn

[Fowmy]

@FlyKick 


I am halfway reading the question, the First point, the date table has to have a continuous date range with complete years. You can start building the dates using MIN and MAX of your data table and make them into full year.

________________________

Did I answer your question? Mark this post as a solution, this will help others!.

Click on the Thumbs-Up icon on the right if you like this reply 🙂

YouTube, LinkedIn

[FlyKick]

Hey Fowmy,

Whilst I haven't checked the entire date table I have checked the time period I'm testing the slicer with and the date table has every day of the month. It's July 2020 and contains everyday from 1st through to 31st. 

Just to clarify I haven't set the Date table as the date table for Power BI to use. I am just referring to it as my date table because it has a unique set of dates that I need in order to filter on both date columns on the tickets table. 

Do you know for certain if the date table not being continuous would cause this issue even when the time period being filtered by includes all the dates for that month?  

If so can you explain the logic to me as I can't for the life of me work out why that would cause any issues? 

Surely it gets the min date from the date table 1st-Jul-2020 and the max date 31-Jul-2020 and passes that to the dates between function to count the rows? 

If both dates exist in the date table and they are continuous (at least for that month) why would it cause incorrect count?