Forum Discussion

FlyKick's avatar
FlyKick
Helper II
6 years ago
Solved

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

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, EnteredDateOnl...
Need Help
  • v-zhenbw-msft's avatar
    v-zhenbw-msft
    6 years ago

    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.

FlyKick's avatar
FlyKick
Helper II
6 years ago

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's avatar
FlyKick
Helper II
6 years ago

Here is the result of adding the ALLEXCEPT function.

 

  • v-zhenbw-msft's avatar
    v-zhenbw-msft
    Community Support
    6 years ago

    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.

  • v-zhenbw-msft's avatar
    v-zhenbw-msft
    Community Support
    6 years ago

    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.