Count Row Cells for last 7 Days

[instdes]

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.

[Agas · Accepted solution]

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]

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]

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]

@Agas 

That was it. Thank you.