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.   Stuc...
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

     

     

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

      • instdes's avatar
        instdes
        Frequent Visitor
        6 years ago

        Agas 

         

        That was it. Thank you.