working out count based on date

[Anonymous]

Hi All,

I'm looking for a solution for this problem.

i have a table such as below:

What i want to do is count the number of items which have moved to done in the last 30 days, based on the created datestamp.

I hope someone can help.

Thanks

[Anonymous · Accepted solution]

Used this solution in the end, as it gave me what i needed.

Done in last 30 Days = IF(AND(AND([OLD_VALUE_FORMATTED]<> "Done", [NEW_VALUE_FORMATTED]="Done"), [CREATED].[Date]>=Today()-30),1,0)

[Anonymous]

To solve this problem, you can use 2 approaches,

1. in case you want to show it in a visual, for example in a key card, you can add the count of items to the key card and add a filter on it for the date column. In the filters section, you can add the Created column to the "filters in this visual" section, click on filter type & select "relative date", select "in the last", add 30 to the textbox, select "dates" in the last column and click on apply filter. The 2nd filter you`ll apply to this visual will be for the status column, value = "done"

2. The second way is to create a calculated column or measure using DAX.

Please mark it as a solution if this works for you!!

Regards, 

SM

[JirkaZ]

That shouldn't be much of an issue imho. 

Something like this should do it:

CALCULATE(COUNTROWS(YourTable), [Old Value] <> "Done", [New Value] = "Done", DATESBETWEEN(MyDateTableDateColumn, Today() - 30, Today()))