Forum Discussion

instdes's avatar
instdes
Frequent Visitor
6 years ago
Solved

Count Row Cells for last 7 Days

I'm trying to output a number by counting how many times a row has a specific string over the past seven days.  The formula continue to output "True" instead of the number I am looking for.

 

Stuck Last 7 Days = CALCULATE(COUNTROWS('Open Records'), 'Open Records'[Issues] = "Stuck" ) <= TODAY()-7
 
Many thanks.
Need Help
  • Agas's avatar
    Agas
    6 years ago

    Hi,

    I would use something like below if I want to count records last seven days.

     

    Stuck Last 7 Days = CALCULATE(COUNTROWS('Open Records'), 'Open Records'[Issues] = "Stuck", 'Open Records'[recorddate] >= TODAY()-7)

    Or

    Stuck Last 7 Days = CALCULATE(COUNT( 'Open Records'[Issues]), 'Open Records'[Issues] = "Stuck", 'Open Records'[recorddate] >= TODAY()-7)

     

     

    You might have a date value in the dataset, I would use that for compairing with the Today()-7 function for last seven days.

     

     

    Thanks,

    Guven

     

     

4 Replies

  • Agas's avatar
    Agas
    Frequent Visitor
    6 years ago

    Hi,

    I would use something like below if I want to count records last seven days.

     

    Stuck Last 7 Days = CALCULATE(COUNTROWS('Open Records'), 'Open Records'[Issues] = "Stuck", 'Open Records'[recorddate] >= TODAY()-7)

    Or

    Stuck Last 7 Days = CALCULATE(COUNT( 'Open Records'[Issues]), 'Open Records'[Issues] = "Stuck", 'Open Records'[recorddate] >= TODAY()-7)

     

     

    You might have a date value in the dataset, I would use that for compairing with the Today()-7 function for last seven days.

     

     

    Thanks,

    Guven

     

     

    • instdes's avatar
      instdes
      Frequent Visitor
      6 years ago

      Thanks Agas 

       

      I updated my Measure to be:

       

      Stuck Last 7 Days = CALCULATE(COUNTROWS('Open Records'), 'Open Records'[Issues] = "Stuck", 'Open Records'[Created] >= TODAY()-7)

       

      and unfortunately I am getting this error:

       

      Couldn't load the data for this visual

      MdxScript(Model) (6, 204) Calculation error in measure 'Open Recurds'[Stuck Last 7 Days]: DAX comparison operations do not support comparing values of type Text with values of type Date. Consider using the VALUE or FORMAT function to convert on of the values.

       

      • Agas's avatar
        Agas
        Frequent Visitor
        6 years ago

        Hi instdes ,

        Please be sure your "Created" column's data type is Date. I assume it is Text.

        1 - Go to "Data" tab from the Power BI desktop. (from the left side)

        2 - Select the "Created" column and click the "Modelling" . (from the top menu).

        3 - Find "Data Type" and make it "Date"

        4- Turn back to the "Report" view. 

         

         

        Thanks,

        Guven