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

     

     

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.