Forum Discussion

FlyKick's avatar
FlyKick
Icon for Helper II rankHelper 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.

v-zhenbw-msft's avatar
v-zhenbw-msft
Icon for Community Support rankCommunity Support
6 years ago

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

      Here is the result of adding the ALLEXCEPT function.

       

      • v-zhenbw-msft's avatar
        v-zhenbw-msft
        Icon for Community Support rankCommunity 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.